postgresql copy table between servers

Question

This question is similar to Copy table structure into new table

I want to write a php script that that makes a copy of a table including all constraints etc and puts that table onto a different server. What's the best way to do this?

I was thinking something like:

create table moo (like old_moo including defaults including constraints including indexes)

But as far as I know this doesn't copy foreign keys or triggers. Also I dont know if it's possible to get that command to work between different servers (so far I haven't been able to find out how...).

Any clues?

Why does it need to be PHP? This really looks like a task that's far better suited to the admin tools that come with postgres (pg_dump, pg_dumpall, etc). You could make a PHP script that exec()s the tool, I suppose, but is this really a job you're going to be doing so often that it needs automation? — GordonM
– GordonM, Commented Jan 27, 2012 at 8:35
it doesn't necessarily need to be php, it just needs to be scripted. Yeah, it's something that's likely to happen often. I'll read up on the dump stuff now. thanks for the input — Sheena
– Sheena, Commented Jan 27, 2012 at 10:22
Your best bet is probably a simple shell script then (BASH or whatever commandline you're using) that dumps remote DB A to a local file with pg_dump, then restores it to remote DB B. — GordonM
– GordonM, Commented Jan 27, 2012 at 12:13

Szymon Lipiński · Accepted Answer · 2012-01-27 09:00:57Z

1

It cannot be done. How do you want to copy table with constraints like foreign key... when you need another table for this constraint? You cannot have foreign keys between servers.

answered Jan 27, 2012 at 9:00

Szymon Lipiński

28.8k17 gold badges81 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sheena Over a year ago

if table A refs table B then I would copy table B across then A. Then I want the foreign key in A (on the new server) to reference B (also on the new server)

Collectives™ on Stack Overflow

postgresql copy table between servers

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related