Any way to dump PGSQL database with update queries?

n00854180t

Well, I'm writing a version control system for my work's website and PGSQL backend. I want to be able to dump the database from the test server with update queries so that I can just run it on the live server after the changes have been tested. Is there any way to get pg_dump to do this, or would I be better off just writing a script to convert the queries myself. Maybe there are even established methods of doing this kind of thing? Any advice is appreciated, thanks.

Sxooter

Update, or insert? If you want insert just use the -d switch. Or do you want something more complex, like just the most recent updates or something?

n00854180t

Basically I just want a dump of all data in the form of update queries, so that I can just take the dump and run it on my live server after testing changes.

Sxooter

So, what about rows that don't exist in the target database, either because their new in the source database or have been deleted from the target database? Pure updates are likely to fail as well.

Is there a problem with basically truncating the tables in the target database, then dumping JUST the data, not schema, from the source, and use that dump to feed the target?

n00854180t

The databases are identical, any schema changes would be handled manually. The reason I'd like to do it with update queries as it would not interrupt the database operation at all, where dropping or deleting would be a major interruption. I just want version control, but I need to be able to update the live database while it's running.

Sxooter

Would it be acceptable to replace all the data on the target database in one big transaction, or do you need to keep the data that's there and just add / update the data from the source server?

If replacing all the data is acceptable, then you could do this:

begin;
truncate table1;
truncate table2;
... etc ...
insert into table1 values (... repeat as needed)

commit;

This would allow all the data to change in one fell swoop.

If you need to keep the data that's in the target database, then you'll do best if you write a script that basically does a select * from the source, then do the updates / inserts on the target database one row at a time. Note that if there are rows in the source that don't have a corresponding fk in the target database, you'll need to insert, otherwise update.