Thread: Correct pg_dumpall Syntax

Correct pg_dumpall Syntax

From
Rich Shepard
Date:
   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

Re: Correct pg_dumpall Syntax

From
Raymond O'Donnell
Date:
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
------------------------------------------------------------------

Re: Correct pg_dumpall Syntax

From
"A. Kretschmer"
Date:
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

Re: Correct pg_dumpall Syntax

From
Andrew Sullivan
Date:
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/

Re: Correct pg_dumpall Syntax

From
Rich Shepard
Date:
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

Re: Correct pg_dumpall Syntax

From
"Joshua D. Drake"
Date:

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



Re: Correct pg_dumpall Syntax

From
Rich Shepard
Date:
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

Re: Correct pg_dumpall Syntax

From
"Joshua D. Drake"
Date:

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




Re: Correct pg_dumpall Syntax

From
Rich Shepard
Date:
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

Re: Correct pg_dumpall Syntax

From
Andrew Sullivan
Date:
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/

Re: Correct pg_dumpall Syntax

From
Sam Mason
Date:
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

Re: Correct pg_dumpall Syntax

From
Rich Shepard
Date:
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