Re: [ADMIN] Postgres 8.3.1 on Vista
I am not able t install the PostgreSQL 8.3.1 version on windows vista anyone tried the same? or have any suggestions? We installed 8.3.1 on a Visa Home Edition laptop. I don't really recall any issue. What happened to you? I know Vista prompts for all sorts of things that you'll have to approve to keep the install moving along. 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] warm database.
Without worrying about debugging your script, have you considered using pg_standby that's in contrib? It works well and you don't have to fuss with scripts unless you're doing something special. David ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Backup of live database
Brian Modra wrote: Sorry to be hammering this point, but I want to be totally sure its OK, rather than 5 months down the line attempt to recover, and it fails... Are you absolutely certain that the tar backup of the file that changed, is OK? (And that even if that file is huge, tar has managed to save the file as it was before it was changed - otherwise I'm afraid that the first part of the file is saved to tar, and then the file is modified, and the last part of the file is saved to tar from the point it was modified - and so therefore not consistent with the first part... And therefore the file has lost its integrity, so even a WAL restore won't help because the base files themselves are corrupt in the tar file? Not sure if the answers you got answered your question or not. Here's my take: 1) If the database is not running, tar works fine. 2) If the database is running, you can ONLY use tar if you also use WAL archiving since the database will not only need the tar files, which will be inconsistent, but also the WAL files (in your $PGDATA/pg_xlog) in order to recover from those inconsistencies. I find this is best if you are creating a warm standby that is keeping a backup database in sync with a primary. 3) If the database is running, use pg_dump to create a consistent backup. 4) No matter what, as previously mentioned, you should test your backup procedures to ensure you can reliably restore. Good luck, David ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] WAL copying includes vacuum, reindex, etc?
When running WAL backups and warm-standby restores (we're currently on PG 8.2), do the vacuumlo changes, vacuum, analyze and reindex type commands get pushed through WAL files to the backup? Or will the backup database not have these maintenance items done? Thanks, David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] large objects space usage
If there are no other tables storing large objects in the database, dropping and reloading the whole database is probably the simplest solution. Otherwise they're going to need to make sure the unwanted LOs have been cleaned out (see contrib/vacuumlo) and then do a VACUUM FULL or similar on pg_largeobject. Does the psql command 'vacuum;' by itself automatically vacuum pg_largeobject along with all of tables in the database? David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] WAL recovery, stop and resume recovery?
LOG: database system was shut down at 2008-01-11 11:40:05 PST LOG: starting archive recovery LOG: restore_command = ~/postgresql/bin/pg_standby -l -d -s 2 -k 20 -t ~/postgresql/restoreWALs/STOP_RESTORE ~/postgresql/restoreWALs %f %p 2 ~/pg_standby.log *LOG: restored log file 00010005001D from archive LOG: invalid record length at 5/1D68 LOG: invalid primary checkpoint record LOG: restored log file 00010005001D from archive LOG: invalid resource manager ID in secondary checkpoint record PANIC: could not locate a valid checkpoint record* LOG: startup process (PID 9219) was terminated by signal 6 LOG: aborting startup due to startup process failure LOG: logger shutting down One more thing is that the error above about the 1D log file above having an invalid record length, etc. is interesting in that if I restore from the TAR backup created for this and start the backup database in recover mode, it manages to process the 1D log file just fine (along with all the previous log files of course). David
Re: [ADMIN] WAL recovery, stop and resume recovery?
No. Once you've done any transactions in the backup DB, its transaction history has diverged from the master and you can't resume tracking the master. It shouldn't even let you try --- what shenanigans did you pull to force it back into recovery mode? Well, I didn't think it was shenanigans, I just stopped the database once it completed the first recovery, ran a few queries, then re-installed the recovery.conf and started it back up like I initially did. I figured this could be an issue, but since I hadn't issued any changes, I had hoped it might work. There's some work being done on allowing read-only queries against an in-recovery database, which I think would satisfy your desire to see if the backup were sane or not. But I wouldn't bet money on that getting into the system anytime soon. It's definitely not something you can cobble up from spare parts. Fair enough. It's probably not a big deal as I'm doing this only because we're new to using WAL copying for a warm standby, and of course we're testing to see that rows inserted, removed, updated, tables added and dropped, indexes added and dropped, etc. are all making it through. It appears that this works like a charm! Is there a way to know how many WAL files I should keep around to ensure I can recover back to a valid primary checkpoint without having to redo the entire backup process on the primary in 8.2, or do I just have to wait for 8.3 and %r option for recovery? Thanks, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] WAL recovery, stop and resume recovery?
Using PG 8.2, I have a database in recovery mode using pg_standby to handle the WAL restores. Is it allowable to have a backup database in recovery mode, then stop recovery (in this case, by putting the trigger file in place to stop pg_standby), check out that the backup db appears up to date, stop the now active backup db, and then restart it in recover mode again to have it resume its backup role? I have had some success doing this, with the restart in recovery showing: LOG: starting archive recovery LOG: restore_command = ~/postgresql/bin/pg_standby -l -d -s 2 -k 20 -t ~/postgresql/restoreWALs/STOP_RESTORE ~/postgresql/restoreWALs %f %p 2 ~/pg_standby.log LOG: restored log file 000100050018 from archive *LOG: invalid xl_info in primary checkpoint record* LOG: using previous checkpoint record at 5/1820 LOG: redo record is at 5/1820; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 0/1535389; next OID: 53990 LOG: next MultiXactId: 1; next MultiXactOffset: 0 *LOG: automatic recovery in progress* LOG: redo starts at 5/1868 But there are times when I do this that it cannot. Is this because the steps are an issue (after all, I did stop recovery and go active briefly, though I didn't update the db during that time, just did \d and select queries to see that DDL and row data were updated on the backup), or is it related to not keeping enough WAL files around (pg_standby -k 20 was chosen, but it's not clear how to select this value, and it sounds like 8.3 gets rid of that issue entirely) to find the 'secondary checkpoint record'. Here's the sort of error I get when it doesn't allow me to restart: LOG: database system was shut down at 2008-01-11 11:40:05 PST LOG: starting archive recovery LOG: restore_command = ~/postgresql/bin/pg_standby -l -d -s 2 -k 20 -t ~/postgresql/restoreWALs/STOP_RESTORE ~/postgresql/restoreWALs %f %p 2 ~/pg_standby.log *LOG: restored log file 00010005001D from archive LOG: invalid record length at 5/1D68 LOG: invalid primary checkpoint record LOG: restored log file 00010005001D from archive LOG: invalid resource manager ID in secondary checkpoint record PANIC: could not locate a valid checkpoint record* LOG: startup process (PID 9219) was terminated by signal 6 LOG: aborting startup due to startup process failure LOG: logger shutting down Thanks, David
[ADMIN] PITR warm-standby with 8.2 setup questions
I'm trying to get WAL file copying working across two systems. It seems pretty straightforward to handle this in the archive_command of the primary, in which I am able to copy the files easily to a staging area for the backup system. On the backup system, I have the recovery.conf pointing to my script, but I have a few questions on how that program should behave, and whether I'd be much wiser to just use pg_standby instead of my own script. 1) I do not want to give the backup PG a file unless it's a complete file. I don't want to give it one that is currently in the middle of being transferred from the primary to the backup system (or a backup copy that failed in the middle). Most of my files are 16777216 bytes, so I initially checked that size. But I found that there are files with a name like '0001000200DC.0020.backup' that is only 272 bytes. Will that file also need to be given to PG recovery, and if so, how are most determining if a small file is complete or just a partial from the copy? 2) On the recovery side, the %p and %f values are giving me %p of 'pg_xlog/RECOVERYXLOG' and %f of '0001000300B1'. Can I confirm that this means my recover program should find a file named '0001000300B1' in my backup system's WAL archive and copy it to the file named 'pg_xlog/RECOVERYXLOG', or should I put it into 'pg_xlog/0001000300B1'? 3) On the backup PG, is it correct that I have to remove backup_label, postmaster.pid and pg_xlog if they are part of the or TAR backup from the primary's PGDATA area? Thanks, David
[ADMIN] 8.2.4 pg_restore on WinXP and pipes
I'm using cygwin on WinXP and upgrading from 8.1 to 8.2.4. Under 8.1, I ran a full backup and stored that as GZIP file. Under 8.2, I tried to restore using the command: gunzip -c backup81.gz | pg_restore -v -O -d mydb This returned an error: pg_restore: [archiver] did not find magic string in file header The above commands work fine under Linux/Unix. What's interesting is that if I just gunzip the file and run it with non-pipe input redirection, the same thing works fine (so the data is good): gunzip backup81.gz pg_restore -v -O -d mydb backup81 gzip backup81 It as if the pipe for stdin isn't working, but the other does. Any ideas? I'd prefer to not have to gunzip and then gzip and use the pipe as this is the common script we use on Linux and aside from this problem, works well when we run the same scripts under cygwin with WinXP. Thanks, David ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] 8.2.4 pg_restore on WinXP and pipes
Yes it does the same thing: $ gunzip mydb.dump.20070819.gz | pg_restore -v -O -d mydb pg_restore: [archiver] did not find magic string in file header Most odd since it seems to work just fine if gunzip first and then use: pg_restore -v -O -d mydb mydb.dump.20070819 David Kevin Grittner wrote: On Tue, Aug 21, 2007 at 7:23 PM, in message [EMAIL PROTECTED], David Wall [EMAIL PROTECTED] wrote: I'm using cygwin on WinXP gunzip -c backup81.gz | pg_restore -v -O -d mydb This returned an error: pg_restore: [archiver] did not find magic string in file header Out of curiosity, does it do the same thing for?: gunzip backup81.gz | pg_restore -v -O -d mydb -Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] OID type creates files that don't go away
I've created several blobs using the OID type. I note that the database stores the "oid" number that represents two files on disk, such as xinv21281 and xinx21281 with the oid in the database showing up as 21281 via a SELECT. But, I deleted a row that contained this oid, and the two 'x' files are still on disk. I then ran a VACUUM, saw that 1 record was reaped, but the two 'x' files remain. Are these 'x' files going to be reused in some manner, or is this a bug that keeps them around despite the fact that the database row for it has been removed? David
