Correct pg_dumpall Syntax

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&gt; 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&gt; 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&gt; 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&gt; Voice: 503-667-4517 Fax: 503-667-8863