Re: [ADMIN] restore database from bare files
Hi Maybe you must reset the WAL's ( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html ) after restoring from tarball if postgres doesn't start. Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan: So I must one way or another run a 7.3, restore the file from the tarball as is (just put them back to /var/lib/pgsql), the databases should be running correctly then (?), then pg_dump it , upgrade to 7.4 and restore from the pg_dump . before running in all this (and I still don't know how I will be able to get a 7.3 on RHEL4 ... ?) is that the correct procedure ? thanks yes, this is the correct way :). Martin ---(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] Checkpoints - what happens actually?
Empric tests have shown that read rrequests seem to still get proceeded whilst write requests get queued until all dirty pages have been written. That's however just testing and looking. What actually happens is a secret. |-Original Message- |From: KÖPFERL Robert |Sent: Montag, 27. Juni 2005 15:22 |To: pgsql-admin@postgresql.org |Subject: [ADMIN] Checkpoints - what happens actually? | | |Hi, | |i went across a checkpoint problematic (90% load, near real |time app). I've |read in pg-documentation what exists about CHECKPOINT and the |configuration |parameters. But there're still many open questions as: | |What actually happens if a checkpoint occours? |OK, all dirty pages of data files get written to disk...but | |Are read requests (simple selects) still possible and quickly answered? | |What happens to wite-operations? Do they get queued or can |they be written |to xlog while the fsync is running? |Is it maybe really the case but wave of pending statements |arises from the |reduced i/o capacity which is left due to the fsync? | |How about increasing the interval more and more? Will this |produce more and |more dirty pages or is it just that a possible restart will |take a longer |time since the xlog is longer? | |Questions over questions. Can you please give me a clearing |little insight. | |---(end of |broadcast)--- |TIP 5: Have you checked our extensive FAQ? | | http://www.postgresql.org/docs/faq | ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Postrgre Integrated App Development
Actually, I haven't done such thing, yet. However the intended method or a possible way is this: Manage schema-snapshots and put them in VCS Keep a file of changes with version stamps (also in VCS). Document each change to the db (so SQL commands you issue to change its struct). So one can upgrade from any schema version to any other higher one. If you happen to radically change the structure (i.e. Make 2 tables of one): Keep the old table. Create the new ones, fill them with current data, truncate the old table and remove all indexes etc. Make it become a view by adding a _RETURN rule. Add insert+delete+update rules. So any old application is able to operate on the new schema like it did on the old one. Even stored procedures keep working. If your apps just use stored procs (which is recommended) you can also change them to use the new tables or what-o-ever. It depends, but this is the principe -Original Message-From: Leander Gillard [mailto:[EMAIL PROTECTED]Sent: Mittwoch, 29. Juni 2005 17:05To: pgsql-admin@postgresql.orgSubject: [ADMIN] Postrgre Integrated App Development We are currently moving a new large web application live and but are concerned that the structure of the tables may change during this process. Since we will have a temendous amount of data flowing in and out and there will be errors it would be nice to understand how others handle this issue. As far as incrementing the move live, backing up data and then if/when something happens how to correct this or combine the data without breaking the system or causing data corruption. -- Leander
Re: [ADMIN] unicode
Thanks. This works fine on Linux, but I could't get it to work on FreeBSD 5.3 - any ideas? On 24.06.2005 16:55, Peter Eisentraut wrote: Use initdb --locale=de_DE.utf8 and that should be all. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] restore database from bare files
OK, I'am not yet at restarting postgres .. but if at get pb then I check that , thanks ! For now , How can I tell from the bare file the mapping between a database name and the number appearing in /var/lib/pgsql/base directory I have : pgsql/data/base/1/ pgsql/data/base/16975/ pgsql/data/base/16980/ so I made the assumption that 1 is a database (probably the test initial database ?), 16975 is an other one and 16980 a tird one ! How can I find the map from these numbers to database name ? thanks Martin Fandel wrote: Hi Maybe you must reset the WAL's ( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html ) after restoring from tarball if postgres doesn't start. Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan: So I must one way or another run a 7.3, restore the file from the tarball as is (just put them back to /var/lib/pgsql), the databases should be running correctly then (?), then pg_dump it , upgrade to 7.4 and restore from the pg_dump . before running in all this (and I still don't know how I will be able to get a 7.3 on RHEL4 ... ?) is that the correct procedure ? thanks yes, this is the correct way :). Martin ---(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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] restore database from bare files
Hi, try this: psql -t -d yourdb -c SELECT datid FROM pg_stat_database WHERE datname='yourdb'; http://www.postgresql.org/docs/8.0/static/monitoring-stats.html Greetings, Martin Am Donnerstag, den 30.06.2005, 12:57 +0200 schrieb jehan-free: OK, I'am not yet at restarting postgres .. but if at get pb then I check that , thanks ! For now , How can I tell from the bare file the mapping between a database name and the number appearing in /var/lib/pgsql/base directory I have : pgsql/data/base/1/ pgsql/data/base/16975/ pgsql/data/base/16980/ so I made the assumption that 1 is a database (probably the test initial database ?), 16975 is an other one and 16980 a tird one ! How can I find the map from these numbers to database name ? thanks Martin Fandel wrote: Hi Maybe you must reset the WAL's ( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html ) after restoring from tarball if postgres doesn't start. Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan: So I must one way or another run a 7.3, restore the file from the tarball as is (just put them back to /var/lib/pgsql), the databases should be running correctly then (?), then pg_dump it , upgrade to 7.4 and restore from the pg_dump . before running in all this (and I still don't know how I will be able to get a 7.3 on RHEL4 ... ?) is that the correct procedure ? thanks yes, this is the correct way :). Martin ---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] unicode
Am Donnerstag, 30. Juni 2005 11:58 schrieb Hannes Dorbath: Thanks. This works fine on Linux, but I could't get it to work on FreeBSD 5.3 - any ideas? I hear that FreeBSD doesn't support Unicode, so you're probably out of luck for now. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] unicode
Hannes Dorbath [EMAIL PROTECTED] writes: I hear that FreeBSD doesn't support Unicode, so you're probably out of luck for now. Hm, but the locales are there, it's just named de_DE.UTF-8 instead of de_DE.utf8 on FreeBSD. InitDB crashs with the following: creating template1 database in /opt/pgsql/data/base/1 ... FATAL: XX000: failed to initialize lc_messages to LOCATION: InitializeGUCOptions, guc.c:2389 We've seen that happen before. As far as I can see it indicates breakage in the locale library: setlocale(LC_MESSAGES, ) should work, but it's failing. I'm not certain this is the total story, since the same failure has been seen on several platforms and it's hard to believe they are all brain-damaged. But I don't really see what we should do differently. regards, tom lane ---(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
[ADMIN] Linux syslog question ...
Just turned syslog logging on on a Linux box, and the queries are coming through like: ^Iincident, building, location, category, remark, building_access,^M with the control characters in them ... it doesn't do this on my FreeBSD box, so figure I'm mis-configuring something in syslog itself on Linux? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] problem with WAL files
I'm currently testing PITR features of 8.0 release. I followed the steps given by the documentation and thus deleted the WAL files in pg_xlog after having restored the database. But when I put the recovery.conf file and try to start postgres, it fails. The log file says : 2005-06-30 17:57:50 LOG: database system was shut down at 2005-06-30 17:57:26 Paris, Madrid 2005-06-30 17:57:50 LOG: starting archive recovery 2005-06-30 17:57:50 LOG: restore_command = copy E:\\temp\\archive\\%f %p 2005-06-30 17:57:50 LOG: recovery_target_time = 2005-07-01 00:45:00+02 2005-06-30 17:57:50 LOG: could not open file C:/Program Files/PostgreSQL/8.0/data/pg_xlog/0001000B (log file 0, segment 11): No such file or directory 2005-06-30 17:57:50 LOG: invalid primary checkpoint record 2005-06-30 17:57:50 LOG: could not open file C:/Program Files/PostgreSQL/8.0/data/pg_xlog/0001000B (log file 0, segment 11): No such file or directory 2005-06-30 17:57:50 LOG: invalid secondary checkpoint record 2005-06-30 17:57:50 PANIC: could not locate a valid checkpoint record This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2005-06-30 17:57:50 LOG: startup process (PID 356) was terminated by signal 3 2005-06-30 17:57:50 LOG: aborting startup due to startup process failure 2005-06-30 17:57:51 LOG: logger shutting down What's the problem ? I have an idea, but can't solve it : When I want to restore the database, the psql -U postgres essai save.txt command fails because it seems that psql read the password in save.txt (it don't let me type it) and fails to connect. So I first connected and then restored with the \i command. Maybe this way of proceeding alter WAL files in a bad way ? I tryed many ways of restoring and deleting/putting WAL files, but all failed. Can somebody help me ? Thanks. Romain Thouvenin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] problem with WAL files
Romain, This is a problem I ran into during testing as well. It seems that your base backup must also include the in use wal file. IN your case: 0001000B. So, I think the restore documentation is slightly misleading. In my experience, what you really need to do is remove all wal files from your pg_xlog directory, then put in the in use wal file from when you did your base backup (0001000B in your case), put in the recovery.conf and then start postgresql. This procedure works well for me, but I didn't quite grasp that initially when reading the documentation on how to backup/restore with PITR. On Thu, 30 Jun 2005, Romain Thouvenin wrote: But when I put the recovery.conf file and try to start postgres, it fails. The log file says : 2005-06-30 17:57:50 LOG: could not open file C:/Program Files/PostgreSQL/8.0/data/pg_xlog/0001000B (log file 0, segment 11): No such file or directory -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Linux syslog question ...
Does the same thing here, which is kind of annoying when you log queries for debugging and can't copy/paste them easily. I think it's a linux syslog bug/feature. On Thursday 30 June 2005 07:32 am, Marc G. Fournier wrote: Just turned syslog logging on on a Linux box, and the queries are coming through like: ^Iincident, building, location, category, remark, building_access,^M with the control characters in them ... it doesn't do this on my FreeBSD box, so figure I'm mis-configuring something in syslog itself on Linux? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] restore database from bare files
very good, that worked fine :-) I restored the files from tar, started a postgresql 7.3 on an old redhat 9 ! pg_dump my database, psql it back to my postgresql 7.4 on my production RHEL4 server . still a small pb, I seem to have lost authentification. (although pg_hba.conf was restore also) $ psql -h meta1 -U ezpublish -d ezpublish_db psql: FATAL: user ezpublish does not exist if I go with: $ psql -h meta1 -U postgres -d ezpublish_db Welcome to psql 7.4.8, the PostgreSQL interactive terminal. that works , then I go for setting a user + password: ezpublish_db-# ALTER USER ezpublish SET PASSWORD secret; ERROR: syntax error at or near $ at character 1 what's wrong ? note that for that ezpublisher database I had initily integrated from postgresql-contribs those functions: $psql ezpublish_db /usr/share/pgsql/contrib/pgcrypto.sql don't know if my problem is related to that ? Thanks again. Martin Fandel wrote: Hi, try this: psql -t -d yourdb -c SELECT datid FROM pg_stat_database WHERE datname='yourdb'; http://www.postgresql.org/docs/8.0/static/monitoring-stats.html Greetings, Martin Am Donnerstag, den 30.06.2005, 12:57 +0200 schrieb jehan-free: OK, I'am not yet at restarting postgres .. but if at get pb then I check that , thanks ! For now , How can I tell from the bare file the mapping between a database name and the number appearing in /var/lib/pgsql/base directory I have : pgsql/data/base/1/ pgsql/data/base/16975/ pgsql/data/base/16980/ so I made the assumption that 1 is a database (probably the test initial database ?), 16975 is an other one and 16980 a tird one ! How can I find the map from these numbers to database name ? thanks Martin Fandel wrote: Hi Maybe you must reset the WAL's ( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html ) after restoring from tarball if postgres doesn't start. Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan: So I must one way or another run a 7.3, restore the file from the tarball as is (just put them back to /var/lib/pgsql), the databases should be running correctly then (?), then pg_dump it , upgrade to 7.4 and restore from the pg_dump . before running in all this (and I still don't know how I will be able to get a 7.3 on RHEL4 ... ?) is that the correct procedure ? thanks yes, this is the correct way :). Martin ---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] restore database from bare files
ezpublish_db-# ALTER USER ezpublish SET PASSWORD secret; ERROR: syntax error at or near $ at character 1 I wonder why you have ezpublish_db-# instead of ezpublish_db=#? I just noticed it, and to me it happens usually when something's been carried over from the previous line. My 2 pence... Regards, Ben Kim Developer College of Education Texas AM University ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] problem with WAL files
Jeff Frost [EMAIL PROTECTED] writes: When I tested with our system, it would not attempt to retrieve that first WAL via the restore command. All the ones after that it was happy to do it for, but if that one did not exist, it told me to get lost. Would you be more specific please? Looking at the code, I can't see a reason that the first one would be treated differently from others. What *exactly* did you get? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] problem with WAL files
On Thu, 30 Jun 2005, Tom Lane wrote: Would you be more specific please? Looking at the code, I can't see a reason that the first one would be treated differently from others. What *exactly* did you get? Unfortunately, I do not have my output saved, but here's basically what happened: I made a base backup as such: * psql to start backup * rsync pgdata dir with --exclude pg_xlog * psql to stop backup Copy this to new server's pgdata dir, put in recovery.conf and start postgres. Postgres reports that it could not find wal archive (which would be the in-use wal during the base backup) and exits fatal. Copying the wal archive to the archive dir received the same warning; however, copying it to the pg_xlog dir allowed the restore to begin and the archived wal files after that happily started replaying. I'll have to retest that particular scenario and verify that something else was not causing the problem when I have a chance now that I have a fully functional and scripted out base backup and recover solution; however, it did not give me any of the errors I see when my walrestore.sh script cannot access an archived wal, so I would believe it did not even try to get it out of the archive. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] problem with WAL files
Jeff Frost [EMAIL PROTECTED] writes: I'll have to retest that particular scenario and verify that something else was not causing the problem when I have a chance now that I have a fully functional and scripted out base backup and recover solution; however, it did not give me any of the errors I see when my walrestore.sh script cannot access an archived wal, so I would believe it did not even try to get it out of the archive. As far as I can see from the code, it *should* try to get it out of the archive ... and I'm quite sure we tested that case during 8.0 development ... so I suspect there is some other contributing factor in your configuration. If you could put together a self-contained test case I'd be happy to dig into it, because it sounds like a bug to me. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
