Alvaro,

I am a newbie, so I essentially invoked pg_dump from with pgAdmin3, with the defaults (including large objects).
This is the command being issued:

..C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe -i -h 172.20.0.32 -p 5432 -U postgres -F c -b -v -f "C:\Documents and Settings\administrator.MS\testbk.backup" events

What I assumed was happening (and I may have very well been wrong) was that I was getting a consistent backup of the object at the time that it was processed, but not the database as a whole.


________________________________

From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Thu 4/20/2006 10:02 PM
To: Benjamin Krajmalnik
Cc: Tom Lane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication



Benjamin Krajmalnik wrote:

> The particular table which was problematic (and for which I posted
> another message due to the unique constraint violation which I am
> seeing intermittently) is the one with the high insertion rate. The
> sequence is currently being used to facilitate purginf of old records.

How are you creating the dumps of the sequence and the table? If you do
both separately (as in two pg_dump invocations with a -t switch each),
that could explain your problem. This shouldn't really happen however,
because the sequence dump should be emitted in a dump of the table, if
the field is really of SERIAL or BIGSERIAL type. However I don't see
any other way which would make the sequence go out of sync.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Benjamin Krajmalnik wrote:

> I am a newbie, so I essentially invoked pg_dump from with pgAdmin3,
> with the defaults (including large objects). This is the command
> being issued:
>
> .C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe -i -h 172.20.0.32 -p 5432 -U postgres -F c -b -v -f "C:\Documents and Settings\administrator.MS\testbk.backup" events
>
> What I assumed was happening (and I may have very well been wrong) was
> that I was getting a consistent backup of the object at the time that
> it was processed, but not the database as a whole.

This command should produce a consistent dump of all the objects in the
database. (Not a consistent view of each object in isolation, which is
AFAIU what you are saying.)

Next question is, how are you restoring this dump?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

1. Dropped database
2. Recreated blank databas
3. C:\Program Files\PostgreSQL\8.1\bin\pg_restore.exe -i -h 172.20.0.32 -p 5432 -U postgres -d events -v "C:\Documents and Settings\administrator.MS\testbk.backup"
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: creating TABLE appointments
pg_restore: executing SEQUENCE SET appointments_id_seq
pg_restore: restoring data for table "appointments"
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql
pg_restore: setting owner and privileges for TABLE appointments
Process returned exit code 0.

(the above is the result of a sample restore - the database is a simple database with very few records, and was backed up when no activity was taking place against it, unlike our production database).

Maybe I am using the wrong switches altogether to accomplish my end results.

Thanks a million for your willingness to point me in the right direction.

________________________________

From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Thu 4/20/2006 10:35 PM
To: Benjamin Krajmalnik
Cc: Tom Lane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication



Benjamin Krajmalnik wrote:

> I am a newbie, so I essentially invoked pg_dump from with pgAdmin3,
> with the defaults (including large objects). This is the command
> being issued:
>
> .C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe -i -h 172.20.0.32 -p 5432 -U postgres -F c -b -v -f "C:\Documents and Settings\administrator.MS\testbk.backup" events
>
> What I assumed was happening (and I may have very well been wrong) was
> that I was getting a consistent backup of the object at the time that
> it was processed, but not the database as a whole.

This command should produce a consistent dump of all the objects in the
database. (Not a consistent view of each object in isolation, which is
AFAIU what you are saying.)

Next question is, how are you restoring this dump?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tom,

Just wanted to let you know that I found the problem with the constraint violation on insert.
When I restored from a backup, I forgot to update the sequences current values to thje macval of the associated tables.
The problem had nothing to do with SP call sequencing, but rather with the sequence assigning a value which was already in use (and was way below the current calue in the table).

After your message concerning the sequence values the light bulb came on in my head and I went to check those.
Now I can go to sleep wothout worrying

Thanks for all your help!

________________________________

From: pgsql-admin-owner@postgresql.org on behalf of Benjamin Krajmalnik
Sent: Thu 4/20/2006 9:52 PM
To: Tom Lane
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication


Tom,


Once again, thank you. Also, did you receive the snippet of the stored procedure which I sent you? As I mentioned, the only place where row insertion is performed is via that stored procedure, and the sequences were created by defining the columns as "bigserial", which still has me puzzled as to why I am experiencing the contraing violation on the unique primary key.

Regards,

Benjamin

________________________________

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thu 4/20/2006 9:09 PM
To: Benjamin Krajmalnik
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Howto: Using PITR recovery for standby replication



"Benjamin Krajmalnik" <kraj@illumen.com> writes:
> I have tried using pg_dump, but discovered that the backup was not a =
> consistent backup.

Really?

> Back to the problem I faced when testing backups with pg_dump, it =
> appears that the backup was not a consistent backup of the data. For =
> example, sequences which are used by some tables bo longer held the =
> correct values (the tables now held higher values),

Sequences are non-transactional, so pg_dump might well capture a higher
value of the sequence counter than is reflected in any table row, but
there are numerous other ways by which a gap can appear in the set of
sequence values. That's not a bug. If you've got real discrepancies
in pg_dump's output, a lot of us would like to know about 'em.

regards, tom lane