Skip site navigation (1) Skip section navigation (2)

Re: Avoid duplicated rows when restoring data from pg_dumpall ??

From: Andy Shellam <andy-lists(at)networkmail(dot)eu>
To: Pablo Alonso-Villaverde Roza <pavroza(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Avoid duplicated rows when restoring data from pg_dumpall ??
Date: 2009-08-26 16:51:51
Message-ID: 4A956827.4060608@networkmail.eu (view raw or flat)
Thread:
Lists: pgsql-admin
Hi Pablo

> - As regards the duplicated rows, no, I don't get duplicated rows in 
> all the tables stored in the database because
> some tables have primary-keys (and/or UNIQUE) constraints. These 
> constraints don't allow the restore process to
> duplicate rows.  In fact, it is a kind of "solution" I've tried...(add 
> an extra column with a primary-key or unique constraint, to this 
> tables), and it "works". The restore process doesn't generate 
> duplicated rows, because the constraint does not allow the insertion
> of new duplicated data.  Anyway..it looks like a kind of 'poor 
> solution'  :-)

Yeah, that's one solution, only trouble being if the data in the 
existing table is different to what's in the restore script (for a 
record with the same ID) it won't be updated.

e.g.: in this example your restored database will be inconsistent with 
the backup.
your table: Field1 = 1 (ID), Field2 = A, Field3 = B
restore script: Field1 = 1 (ID), Field2 = B, Field3 = B

>
> - Ok, thanks for the info, I thought pg_dumpall would work as I 
> desired even on non-empty clusters.
> Of course...if there is no previous data, the restore process will 
> never create duplicated rows.

Exactly.  If you're looking for some form of replication (i.e. 
master-to-slave) look at Slony - it fires triggers on the master that 
insert data into the slave.  It has its limitations but AFAIK it's a 
workable solution.
>
> - Yes, the restore process generates errors, because it tries to 
> re-generate data structures that exist in the database server
> at that moment. Even if I delete my own databases, some errors will 
> appear (because I cannot delete the internal stuff of
> of the server -> the 'postgres' database for example) . Those errors, 
> could be ignored in most cases I think, but perhaps create a kind of 
> "bad feeling" about the result of the restore process,  or can "hide" 
> other more important errors when you get a huge ammount of info on the 
> screen either.

You can delete the "postgres" database - it's an empty database that's 
created when the server is initialised so you've got something to 
connect to.  It's safe to delete, as long as you have another database 
you can connect to, but there's no real reason to unless it's in your 
restore script (e.g. from pg_dumpall.)

http://www.postgresql.org/docs/8.3/static/manage-ag-templatedbs.html

Regards,
Andy

In response to

Responses

pgsql-admin by date

Next:From: Carol WalterDate: 2009-08-26 18:23:56
Subject: Re: Primary key on existing table?
Previous:From: OliveirosDate: 2009-08-26 15:22:07
Subject: Re: Trouble with postgres user's password on Windows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group