Re: snapshot replication with pg_dump

Hello,

Does anyone have any good examples of implementing "snapshot" replication.
I know that PostgreSQL does not have snapshot replication and that Slony-I
is the recomended replication senario but I've configured it and it seems
rather advanced for a shop that is implementing PostgreSQL for the first
time. I have an application that will be mostly reads and snapshot
replication would probably be simple enough and would work. I was thinking
about just using pg_dump to do the trick because the DB should not get very
large. Does anyone have any advanced examples of doing something like this?
Also, does anyone have any comments they'd like to share about this...

Thanks in advance,

Paul

--
View this message in context: http://www.nabble.com/snapshot-replication-with-pg_dump-tf2090351.html#a5761329
Sent from the PostgreSQL - hackers forum at Nabble.com.

plabrh1(at)gmail(dot)com (Paul Silveira) writes:
> Does anyone have any good examples of implementing "snapshot"
> replication. I know that PostgreSQL does not have snapshot
> replication and that Slony-I is the recomended replication senario
> but I've configured it and it seems rather advanced for a shop that
> is implementing PostgreSQL for the first time. I have an
> application that will be mostly reads and snapshot replication would
> probably be simple enough and would work. I was thinking about just
> using pg_dump to do the trick because the DB should not get very
> large. Does anyone have any advanced examples of doing something
> like this? Also, does anyone have any comments they'd like to share
> about this...

If your database is small, and your needs simple, then using pg_dump
to generate "snapshots" is a perfectly reasonable idea.

I suppose the primary complication is whether or not you have multiple
databases around on the cluster... If you don't, or if they all need
to be "snapshotted," you might consider using pg_dumpall, which also
creates users and databases.

If pg_dumpall is unsuitable, then you'll still need to grab user
information that isn't part of pg_dump output...
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/postgresql.html
"This .signature is shareware. Send in $20 for the fully registered
version..."

Yes the needs are simple. I was also thinking about using DBI. The most
important thing to me is that everything is kept in a transaction so that
users can still read the data while I'm snapshotting it at the same time.
If my transaction is isolated from all the reads happening, then it
shouldn't matter how long it takes for me to move the data over (granted,
that will increase latency, but in this project that's not really too
sensitive) and it will be transparent to the end users.

Does anyone have any examples of using pg_dump in a transaction with a
DELETE or TRUNCATE command? I have begun writing this to get the job
done...

cat DELETE.sql COPYDATA.sql | psql -Upostgres -dMyDBName -hTestServer2

This command will combine the two sql files that I have (the first one just
deletes all from a certain table and the second one is a COPY command from a
previous pg_dump of a specific table) and then it pipes that out to psql to
run it on the remote server.

I like what I have so far but would like to make it more dynamic. If I
could eliminate the need for the two .sql files and make it all happen
within the command line, that would rock.

I guess I'd need something like this... (Pseudo code...)

cat "DELETE FROM MyTable" pg_dump MyDBName -hTestServer1 -a -tMyTableName |
psql -Upostgres -dMyDBName -hTestServer2

I'm not sure how to cat the DELETE at the beginning of the COPY command that
would be delivered from the pg_dump and then pipe that complete thing to the
remote server to be executed as a transaction so that users could still read
from that able while my command was running.

Any ideas???

Thanks in advance,

Paul

Christopher Browne-4 wrote:
>
> plabrh1(at)gmail(dot)com (Paul Silveira) writes:
>> Does anyone have any good examples of implementing "snapshot"
>> replication. I know that PostgreSQL does not have snapshot
>> replication and that Slony-I is the recomended replication senario
>> but I've configured it and it seems rather advanced for a shop that
>> is implementing PostgreSQL for the first time. I have an
>> application that will be mostly reads and snapshot replication would
>> probably be simple enough and would work. I was thinking about just
>> using pg_dump to do the trick because the DB should not get very
>> large. Does anyone have any advanced examples of doing something
>> like this? Also, does anyone have any comments they'd like to share
>> about this...
>
> If your database is small, and your needs simple, then using pg_dump
> to generate "snapshots" is a perfectly reasonable idea.
>
> I suppose the primary complication is whether or not you have multiple
> databases around on the cluster... If you don't, or if they all need
> to be "snapshotted," you might consider using pg_dumpall, which also
> creates users and databases.
>
> If pg_dumpall is unsuitable, then you'll still need to grab user
> information that isn't part of pg_dump output...
> --
> (reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
> http://www3.sympatico.ca/cbbrowne/postgresql.html
> "This .signature is shareware. Send in $20 for the fully registered
> version..."
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
View this message in context: http://www.nabble.com/snapshot-replication-with-pg_dump-tf2090351.html#a5907049
Sent from the PostgreSQL - hackers forum at Nabble.com.

On Mon, Aug 21, 2006 at 06:40:22AM -0700, Paul Silveira wrote:
>
> Yes the needs are simple. I was also thinking about using DBI. The most
> important thing to me is that everything is kept in a transaction so that
> users can still read the data while I'm snapshotting it at the same time.
> If my transaction is isolated from all the reads happening, then it
> shouldn't matter how long it takes for me to move the data over (granted,
> that will increase latency, but in this project that's not really too
> sensitive) and it will be transparent to the end users.

Looks to me like the -c option to pg_dump should do what you want.

<snip>

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Can you do that if you have functions tied to the table? Also would that be
in a transaction? I need to allow seamless usability to the data while I'm
doing this snapshot. Not sure the -c option (Clean Drop schema) would work
here. I want to only drop a table and not the entire db so that I'm not
moving data that doesn't need to be moved.

The goal is to only shapshot data in tables that has changed. I would like
to wrap that in a transaction.

-Paul

Martijn van Oosterhout wrote:
>
> On Mon, Aug 21, 2006 at 06:40:22AM -0700, Paul Silveira wrote:
>>
>> Yes the needs are simple. I was also thinking about using DBI. The most
>> important thing to me is that everything is kept in a transaction so that
>> users can still read the data while I'm snapshotting it at the same time.
>> If my transaction is isolated from all the reads happening, then it
>> shouldn't matter how long it takes for me to move the data over (granted,
>> that will increase latency, but in this project that's not really too
>> sensitive) and it will be transparent to the end users.
>
> Looks to me like the -c option to pg_dump should do what you want.
>
> <snip>
>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
>> From each according to his ability. To each according to his ability to
>> litigate.
>
>

--
View this message in context: http://www.nabble.com/snapshot-replication-with-pg_dump-tf2090351.html#a5908347
Sent from the PostgreSQL - hackers forum at Nabble.com.