[ADMIN] PostgreSQL backup idea
Dear all, I have been thinking about PostgreSQL backup for some time now. I can't implement PITR right now on our live systems, for commercial reasons. I have been backing up the server with PG_DUMP every two days, reason it takes some times more than 24 Hours to backup the full database. I have an idea not sure how workable it is: 1 - Backup live server pipe the content of pg_dump to psql and restore it to the second database server. I have tested this with a small database on my test model and it works, not sure how long it will take though. 2 - I also thought, once I have backed up the full database, I am going to see if it is possible to check which tables have changed and only backup those to the remote server. Not sure if it is possible to figure out which tables have changed, is there log or some command which can tell me which tables have changed? 3 - Another idea would be, to backup the full DB and then check when was the last update and from there do a backup and restore remotely. For example: Last update was at 13:00, so from 13:00 onwards I would copy all the records and restore on the remote server. Is it possible to find out what was the last minute which we had an update, and then backup only the records which were updated to the current time? If so, how would I go about to do that? 4 - Backup instead of time use transactionID Do a full backup, mark what was the last transactionID to the minute of backup finish and then onwards to backups only for the updated transactionIDs. For example: Full backup finishes at 13:00, the last transactionID at 13:00 would be 00013, then from 13:01 onwards backup the updates, so on. I am not sure if some of these things are possible, these are only ideas and I would appreciate any input and help, in either build it or destroying it. If anyone has a backup script which handles failure and emails out and would like to share, for me to study it, I would very much appreciate it. If you need more details why, reasons etc, please email me and I will clarify. I am trying to work around the problems I am facing currently. Thank you very much. Really appreciate any help and input Best regards Renato Renato Oliveira e-mail: renato.olive...@grant.co.uk Tel: +44 (0)1763 260811 Fax: +44 (0)1763 262410 http://www.grant.co.uk/ Grant Instruments (Cambridge) Ltd Company registered in England, registration number 658133 Registered office address: 29 Station Road, Shepreth, CAMBS SG8 6GB UK P Please consider the environment before printing this email CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies. VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s). OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our http://www.grant.co.uk/Support/openxml.html -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL backup idea
Renato Oliveira wrote: > Dear all, > > I have been thinking about PostgreSQL backup for some time now. > > I can't implement PITR right now on our live systems, for commercial > reasons. I think you need to rethink this one... > 4 - Backup instead of time use transactionID Do a full backup, mark > what was the last transactionID to the minute of backup finish and > then onwards to backups only for the updated transactionIDs. For > example: Full backup finishes at 13:00, the last transactionID at > 13:00 would be 00013, then from 13:01 onwards backup the updates, so > on. This is conceptually PITR. -- Jesper -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL backup idea
Renato Oliveira wrote: > I can't implement PITR right now on our live systems, for > commercial reasons. What do you mean? Most of your email seems to describe techniques very much like PITR; why would that be OK but the existing, tested PITR not be OK? It's hard to know what to suggest without understanding the answers to those questions. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL backup idea
On Wed, Jan 20, 2010 at 6:48 AM, Kevin Grittner wrote: > Renato Oliveira wrote: > > > I can't implement PITR right now on our live systems, for > > commercial reasons. > > What do you mean? Most of your email seems to describe techniques > very much like PITR; why would that be OK but the existing, tested > PITR not be OK? It's hard to know what to suggest without > understanding the answers to those questions. > PITR is a one or two line update to the postgresql.conf + a base backup. What you're talking about is trying to build your own version of this (significantly more complex). What are the 'commercial' reasons that you have for not using PITR, ISTM that you'd really be wasting time not using it. --Scott
[ADMIN] Warm standby problems: SOLVED
Hi, Back in October 2009, I reported on strange warm-standby problems in this thread: http://archives.postgresql.org/pgsql-admin/2009-10/msg00170.php Just in case anyone still cares or is wondering, we found the problem. The machine had bad RAM; we were getting undetected/uncorrected single-bit errors creeping through! The thing that led to the discovery was a cron job error complaining about a "SEHECT" statement when the Perl script clearly read "SELECT". We swapped the RAM a while back and the problem seems to have been cured. Regards, David. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL backup idea
I just noticed that the list has not been copied on most of this exchange. Please keep the list copied, as others are likely to contribute useful ideas. Renato Oliveira wrote: > Can I ask few questions about it, if possible? Sure. Please keep it "on list", though. > We are running Linux Redhat 4. That should make building from source pretty easy. > [postg...@78674-db1 ~]$ pg_config > VERSION = PostgreSQL 8.2.4 > [postg...@db2 ~]$ pg_config > VERSION = PostgreSQL 8.3.4 Well, there's your problem, right there. Your primary is 8.2.4, but your secondary is 8.3.4. The other information matches nicely and there were no special build options -- it's just that there are two different major versions of PostgreSQL here, and PITR backups definitely won't deal with that. Is there a reason for having your secondary server on a newer major release (like there was some query which wouldn't run correctly or within a reasonable amount of time without it)? Wait -- I just had a thought. Old version copying to new version. Slony in the mix. This sounds like a situation where the old DBA was trying to use Slony to upgrade with minimal down time. Problem was, you haven't been able to keep the newer version up-to-date through Slony, so you couldn't cut over? If that's the situation, it changes the focus. And it also means we need someone who understands Slony in the discussion -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm standby problems: SOLVED
On Wed, Jan 20, 2010 at 10:10:26AM -0500, David F. Skoll wrote: > The machine had bad RAM; we were getting undetected/uncorrected single-bit > errors creeping through! who's the machine/memory vendor? -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Warm standby problems: SOLVED
Ray Stell wrote: >> The machine had bad RAM; we were getting undetected/uncorrected single-bit >> errors creeping through! > who's the machine/memory vendor? I don't know exactly; it's a colocated machine that we don't own. dmidecode (trimmed down) says: System Information Manufacturer: Supermicro Product Name: C2SBM-Q Version: 0123456789 [...] Memory Device Error Information Handle: No Error Size: 2048 MB Form Factor: DIMM Bank Locator: DIMM 2 Type: DDR2 Type Detail: Synchronous Speed: 800 MHz (1.2 ns) Manufacturer: Kingston Serial Number: 0DCC6845 But that's the good memory... I don't know what the bad memory was. Regards, David. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] last insert/update for a table
Hi all; is there a system catalog query or function that will show me the last time an insert or update occurred for a specified table ? Thanks in advance -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL backup idea
On Wed, 2010-01-20 at 10:04 -0600, Kevin Grittner wrote: > I just noticed that the list has not been copied on most of this > exchange. Please keep the list copied, as others are likely to > contribute useful ideas. > > Renato Oliveira wrote: > > > Can I ask few questions about it, if possible? > > Sure. Please keep it "on list", though. > > > We are running Linux Redhat 4. > > That should make building from source pretty easy. > > > [postg...@78674-db1 ~]$ pg_config > > VERSION = PostgreSQL 8.2.4 > > > [postg...@db2 ~]$ pg_config > > VERSION = PostgreSQL 8.3.4 > > Well, there's your problem, right there. Your primary is 8.2.4, but > your secondary is 8.3.4. The other information matches nicely and > there were no special build options -- it's just that there are two > different major versions of PostgreSQL here, and PITR backups > definitely won't deal with that. > > Is there a reason for having your secondary server on a newer major > release (like there was some query which wouldn't run correctly or > within a reasonable amount of time without it)? > > Wait -- I just had a thought. Old version copying to new version. > Slony in the mix. This sounds like a situation where the old DBA > was trying to use Slony to upgrade with minimal down time. Problem > was, you haven't been able to keep the newer version up-to-date > through Slony, so you couldn't cut over? If that's the situation, > it changes the focus. And it also means we need someone who > understands Slony in the discussion If the issue here is the OP wants to move from 8.2.4 to 8.3.9 (as opposed to 8.3.4) with Slony, here is what to do. 1: Build 8.3.9 from source. 2: Build the same Slony version of slony that is running against the 8.3.9 tree 3 : Upgrade the 8.3.4 version to 8.3.9 (Slony is irrelevant here - normal minor version PG upgrade) 4: When ready to upgrade, issue the Slony Move Master command to move from the 8.2.4 node to the 8.3.9. 5: If you still need a Slony replica - drop the 8.2.4 node from replication and rebuild it as an 8.3.9 replica. OP if you need any more information about the Slony portions of this - please ask them on the Slony list. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] vacuum + autovacuum
Hi, i have some questions regarding - behavior of vacuum and autovacuum after changing configuration and - interaction between those two i change the vacuum_cost_delay parameter to 0 in postgresql.conf and reloaded the server (sighup). does that have an immediate effect on running vaccuums and autovacuum processes? for me it seems not, but i'd like to know it to better understand and evaluate the i/o statistics i see. I changed the vacuum_cost_delay from 200 to 0 without seeing an immediate effect. I saw an effect when i startet new vacuums. But I did not see an effect on new autovacuum processes or running processes not even after hours. autovacuum_vacuum_cost_delay is set to -1. So for my understanding it should have an effect on autovacuum. Then I changed autovacuum_vacuum_cost_delay to 200, reloaded the server, set it back to -1 and reloaded the server again. Now I see an effect on autovacuum. unfortunately I don't have comparable statistics, but in a few hours or days i'll have them. The question is if i'm right on my manual observations and if so if this can be improved. or if there is a workaround for this. My goal is to configure as generic as possible autovacuum to get the best throughput - or is there already work on this what could be shared. I'd like to change settings based on current i/o statistics and planned cronjobs like backups etc. the second question i have is about knowing postgres what is doing vacuum and autocacuum at the same time. my concern is about running manually a vacuum verbose tc.b1234competition;and seeing that autovacuum is stating shortly afterwards exactly the same sql. So for my understanding it would be great to send autovacuum somehow an information that there is a manual run of this and that vacuum/analyze. Does autovacuum look in the the columns pg_stat_all_tables.last_vacuum and pg_stat_all_tables.last_analyze or does it only use the autovac columns? Or better: does it make sense to run a manual vacuum if i have autovacuum running at the same time? postgres=# select * from pg_stat_activity where current_query<>'' and usename='postgres'; datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port ---+--+-+--+--+--+-+---+---+---+-+- 16396 | bd | 11887 | 10 | postgres | autovacuum: ANALYZE uc.bd_user_session_statistic | f | 2010-01-21 07:49:43.793259+01 | 2010-01-21 07:49:43.793259+01 | 2010-01-19 17:22:26.260505+01 | | 16396 | bd | 188 | 10 | postgres | vacuum verbose tc.b1234competition; | f | 2010-01-21 08:08:34.194203+01 | 2010-01-21 08:08:34.194203+01 | 2010-01-21 08:08:34.177298+01 | | -1 16396 | bd |1836 | 10 | postgres | autovacuum: VACUUM tc.b1234competition | t | 2010-01-21 08:23:19.696658+01 | 2010-01-21 08:23:19.696658+01 | 2010-01-21 08:11:10.398047+01 | | 16396 | bd |4431 | 10 | postgres | autovacuum: VACUUM ANALYZE m123service.jms_message_log_entry | f | 2010-01-19 13:12:52.284217+01 | 2010-01-19 13:12:52.284217+01 | 2010-01-19 11:57:21.380041+01 | | 11511 | postgres |5960 | 10 | postgres | select * from pg_stat_activity where current_query<>'' and usename='postgres'; | f | 2010-01-21 08:23:31.67539+01 | 2010-01-21 08:23:31.67539+01 | 2010-01-21 08:13:36.422138+01 | | -1 (5 rows) best regards, Uwe
