Thread: Correct pg_dumpall Syntax
I do not see an option on the man page for pg_dumpall that directs it to the data of a different version on a different filesystem. I would greatly appreciate learning the correct syntax that will allow me to use the pg_dumpall from 8.3.3 to extract all data from the 8.1.4 version residing in /usr4/pgsql_old/data/ and write it to a file (with the -f option) in /usr4/postgres-backups. There are no postmaster or postgres processes running. Thanks, Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On 18/06/2008 15:16, Rich Shepard wrote: > I do not see an option on the man page for pg_dumpall that directs it to > the data of a different version on a different filesystem. I would greatly > appreciate learning the correct syntax that will allow me to use the > pg_dumpall from 8.3.3 to extract all data from the 8.1.4 version > residing in > /usr4/pgsql_old/data/ and write it to a file (with the -f option) in > /usr4/postgres-backups. > > There are no postmaster or postgres processes running. As I understand it, pg_dump (and therefore pg_dumpall also) needs a server process running: you direct it to the correct host with the -h option. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
am Wed, dem 18.06.2008, um 7:16:11 -0700 mailte Rich Shepard folgendes: > I do not see an option on the man page for pg_dumpall that directs it to > the data of a different version on a different filesystem. I would greatly > appreciate learning the correct syntax that will allow me to use the > pg_dumpall from 8.3.3 to extract all data from the 8.1.4 version residing in > /usr4/pgsql_old/data/ and write it to a file (with the -f option) in > /usr4/postgres-backups. > > There are no postmaster or postgres processes running. Impossible, pg_dump needs a running db. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Wed, Jun 18, 2008 at 07:16:11AM -0700, Rich Shepard wrote: > pg_dumpall from 8.3.3 to extract all data from the 8.1.4 version residing in > /usr4/pgsql_old/data/ and write it to a file (with the -f option) in > /usr4/postgres-backups. Can't do it. Start the old postmaster with -D /usr4/pgsql_old/data, and then use pg_dumpall against that backend. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
On Wed, 18 Jun 2008, Andrew Sullivan wrote: > Can't do it. Start the old postmaster with -D /usr4/pgsql_old/data, and > then use pg_dumpall against that backend. Andrew, When I try, I see: postgres@salmo:/var/lib/pgsql$ postgres -D /usr4/pgsql_old/data FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 8.1, which is not compatible with this version 8.3.3. Am I correct that the 8.1.4 executables have been replaced with the 8.3.3 ones during the upgrade? Perhaps I should restore /var/lib/pgsql/data from the backup tape, as well as /usr/share/postgresql/, /usr/include/postgresql/, and /usr/bin/postgres? Thanks, Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Wed, 2008-06-18 at 08:55 -0700, Rich Shepard wrote: > On Wed, 18 Jun 2008, Andrew Sullivan wrote: > > > Can't do it. Start the old postmaster with -D /usr4/pgsql_old/data, and > > then use pg_dumpall against that backend. > > Andrew, > > When I try, I see: > > postgres@salmo:/var/lib/pgsql$ postgres -D /usr4/pgsql_old/data > FATAL: database files are incompatible with server > DETAIL: The data directory was initialized by PostgreSQL version 8.1, which > is not compatible with this version 8.3.3. > > Am I correct that the 8.1.4 executables have been replaced with the 8.3.3 > ones during the upgrade? > > Perhaps I should restore /var/lib/pgsql/data from the backup tape, as well > as /usr/share/postgresql/, /usr/include/postgresql/, and /usr/bin/postgres? Your upgrade process that I saw originally has put you in a bad state. This is what I suggest: download 8.1.13: http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fsource% 2Fv8.1.13%2Fpostgresql-8.1.13.tar.gz unpack; then: ./configure --prefix=/tmp/pg813; make install cd /tmp/pg813 bin/pg_ctl -D /usr4/pgsql_old/data start cd / /usr/bin/pg_dumpall -U <user> > mydatabase.sql At this point you will have an 8.3 dump of your 8.1 data. Then you can reinitialize a new cluster with initdb here: /var/lib/pgsql/data (you will have to remove the old one) Then restore as normal using psql -U postgres < mydatabase.sql Joshua D. Drake
On Wed, 18 Jun 2008, Joshua D. Drake wrote: > This is what I suggest: > > download 8.1.13: > unpack; then: Done. > ./configure --prefix=/tmp/pg813; make install > cd /tmp/pg813 > bin/pg_ctl -D /usr4/pgsql_old/data start > cd / > /usr/bin/pg_dumpall -U <user> > mydatabase.sql Modified above a bit. I used /usr3/pg813, needed to su to postgres to start the daemon, and cd to /usr4/pgsql_old/ to run /usr/bin/pg_dumpall. That file now exits in /usr4/postgres-backups and is larger than yesterday's attempt. > At this point you will have an 8.3 dump of your 8.1 data. Looks good to me. > Then you can reinitialize a new cluster with initdb here: > /var/lib/pgsql/data (you will have to remove the old one) As user postgres, I cleaned out /var/lib/pgsql/data/* and re-initialized. I had to specify -E UTF8 because 8.3.3 is not finding en_US as a valid locale coding. > Then restore as normal using psql -U postgres < mydatabase.sql Can't get here. As user postgres, I shut down the 8.1.13 process. However, I cannot start the 8.3.3 daemon running. I tried as user postgres with the pg_ctl command, and as root running the formerly-working '/etc/rc.d/rc.postgresql start' command. What I see with the latter is: [root@salmo /etc/rc.d]# ./rc.postgresql start Starting PostgreSQL 18162 PostgreSQL daemon already running Warning: Missing pid file /var/lib/pgsql/data/postmaster.pid But, there is no postmaster process running despite having the process ID of 18162 shown. Sure enough, the pid file is not present, nor is process 18162 there. Much closer, Josh, but not quite there yet. Many thanks, Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Wed, 2008-06-18 at 10:23 -0700, Rich Shepard wrote: > On Wed, 18 Jun 2008, Joshua D. Drake wrote: > > Then you can reinitialize a new cluster with initdb here: > > /var/lib/pgsql/data (you will have to remove the old one) > > As user postgres, I cleaned out /var/lib/pgsql/data/* and re-initialized. > I had to specify -E UTF8 because 8.3.3 is not finding en_US as a valid > locale coding. > You need to remove the directory, not the files underneath then: initdb -E UTF8 -D /var/lib/pgsql/data > > Then restore as normal using psql -U postgres < mydatabase.sql > Then pg_ctl -D /var/lib/pgsql/data and try restore. > Can't get here. > > As user postgres, I shut down the 8.1.13 process. However, I cannot start > the 8.3.3 daemon running. I tried as user postgres with the pg_ctl command, > and as root running the formerly-working '/etc/rc.d/rc.postgresql start' > command. What I see with the latter is: > > [root@salmo /etc/rc.d]# ./rc.postgresql start Ignore all slackware implementation for the moment. Let's get your data up and running, then we can worry about administrativia. Joshua D. Drake
On Wed, 18 Jun 2008, Joshua D. Drake wrote: > You need to remove the directory, not the files underneath then: Ah, so. Redid, after removing /var/lib/pgsql/data >>> Then restore as normal using psql -U postgres < mydatabase.sql Postgres is now running (whew!), but I'm still doing something incorrectly. In /usr4/postgres-backups, I did: [rshepard@salmo /usr4/postgres-backups]$ pg_restore -U postgres < pg814data.sql pg_restore: [archiver] input file does not appear to be a valid archive But it was created using the 8.3.3 pg_dumpall in /usr/bin/. Much closer, Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Wed, Jun 18, 2008 at 10:42:51AM -0700, Rich Shepard wrote:
> [rshepard@salmo /usr4/postgres-backups]$ pg_restore -U postgres < pg814data.sql
> pg_restore: [archiver] input file does not appear to be a valid archive
>
> But it was created using the 8.3.3 pg_dumpall in /usr/bin/.
Did you use a non-ascii dump format? Try
psql -U postgres -f pg814data.sql
A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/
On Wed, Jun 18, 2008 at 10:42:51AM -0700, Rich Shepard wrote: > [rshepard@salmo /usr4/postgres-backups]$ pg_restore -U postgres < pg814data.sql > pg_restore: [archiver] input file does not appear to be a valid archive > > But it was created using the 8.3.3 pg_dumpall in /usr/bin/. pgdumpall (and pgdump by default) will produce output in SQL format. Restoring is just a simple matter of: psql dbname -f pg814data.sql Hope that helps! Sam
On Wed, 18 Jun 2008, Andrew Sullivan wrote: > Did you use a non-ascii dump format? Try Andrew, Not by design. > psql -U postgres -f pg814data.sql Well! That stirred things up. I seem to have restored the accounting data (and the other databases in the cluster), but cannot access them. As a user, if I type 'psql aesi [Enter]', I'm connected to the database, and aesi-# \d lists all the tables. So, I assume that the restoration worked as intended. But, when I point firefox to http://localhost/sql-ledger/login.pl and try to log in, I get a server error. It's in /var/log/apache/error.log, so that's where I need to start fixing what broke. Thanks to everyone here. I think the upgrade has finally been successful. I learned a lot, and will try to make the next upgrade both much sooner and more smooth. Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863