incremental export and import postgresql C#

Question

I have a PostgreSQL Database, with one table. Each day, I want to export the data WHERE date='whatever' so it ONLY dump the data I've managed TODAY.

Then, I go to another Database, and import that DUMP file, but instead of overwrite what I already had, I want to append to it...

I'm trying to do this on a C# Console APP... Any suggestion?

Thank you.

It is necessary for the client to use PostgreSQL, he doesn't want MSSQL, MySQL, etc.. just PostgreSQL... What a shame.. — Robert W. Hunter
– Robert W. Hunter, Commented Jun 12, 2013 at 15:30
Daniel is correct. It seems like you are trying to write your own replication system. If you need to modify/tweak the data then I suggest you make sure you have a trigger on the desired tables to timestamp them and you take a look at dblink. I often use it to query one db, and pull data into another db after making some changes. Worse case, take a look at copy to and copy from. No need to use a dump file. — David S
– David S, Commented Jun 12, 2013 at 16:00
And by the way, saying that using PostgreSQL is a "shame" is not going to make you many friends or encourage people to help you. Given the fact that you don't know anything about dblink or "copy", it seems unlikely that you have enough experience with PostgreSQL to make such a statement. — David S
– David S, Commented Jun 12, 2013 at 16:04

Craig Ringer · Accepted Answer · 2013-06-12 23:59:30Z

Don't do it this way. Use one of the existing well-maintained solutions for the purpose, like Bucardo, Londiste, Slony-I, etc.

See replication on the Pg wiki.

Londiste at least can cope with being stopped, then resumed when you want it to catch up, so you can run it as a daily batch if you want.

If all you're dealing with is an insert-only table then you can avoid the need for full-fledged replication; all you need is something like

psql -h host1 db1 -c \
    "\copy (SELECT * FROM the_table WHERE the_date = '2012-01-01') TO stdout" \
| psql -h host2 db2 -c \
    "\copy the_table FROM STDIN"

See the manual on COPY

You can do the same thing within your C# app by making two PostgreSQL connections, doing a COPY FROM on one, and a COPY TO on the other, then copying the rows between them. You'll find nPgSQL's support for COPY useful for this.

Collectives™ on Stack Overflow

incremental export and import postgresql C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related