0

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?

Community
  • 1
  • 1
Sheena
  • 15,590
  • 14
  • 75
  • 113
  • 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 Jan 27 '12 at 08: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 Jan 27 '12 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 Jan 27 '12 at 12:13

1 Answers1

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.

Szymon Lipiński
  • 27,098
  • 17
  • 75
  • 77
  • 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) – Sheena Jan 27 '12 at 09:05