Re: Unable to dump database using pg_dump

Adam Dear wrote:
> Let me ask this question. Is there any other option for moving data
> from one server to another that doesn't involve pg_dump? If I can get
> the data out of this server, and onto my new one I don't care if the old
> server gets fixed. I just need the data. The new server already has
> the table structure, I just need the most current data to put into the
> server.

If that's all you need you may be able to use COPY (server side files)
or psql's \copy command (client side files) to dump and load each table.
Since your database is pretty messed up you might not even be able to
connect with psql - so I'd go for using server side COPY.

I don't know if it's safe to run COPY on a server in recovery mode, but
I don't really see why it wouldn't be. You have already made a backup of
your failed data directory anyway, after all, so the risk should be
pretty minimal anyway.

If you haven't taken a backup, I VERY strongly suggest doing so before
doing anything more. The database server should not be running when you
make the backup of the pg data directory (if you're doing a simple file
copy, that is).

For how to use COPY see "\h COPY" in psql or "man copy". In its simplest
form it's just (eg):

COPY customer TO '/some/path/on/server';

... where the postgres user has the right to write to /some/path . If
you wanted to save the data into /home/myname, you might do something
like the following in a root shell:

mkdir /home/myname/pgdump
chmod 700 /home/myname/pgdump
chown postgres /home/myname/pgdump

... then use:

COPY mytable TO '/home/myname/pgdump/mytable';
COPY myothertable TO '/home/myname/pgdump/myothertable';

... etc.

The target path can even be on NFS mount or similar, so if you want you
can dump your data directly onto your new server for easy loading.

For loading, COPY FROM works much like COPY TO.

It might be necessary to disable/drop triggers when you load the data,
then re-enable/recreate them afterwards. For decent performance you
might also want to drop foreign key constraints and indexes during bulk
load with COPY, then recreate them afterwards.

pg_dump and pg_restore do all this for you, but nothing stops you from
doing it manually.

--
Craig Ringer