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
