Thread: Loading Data Dumps for Tables Containing BDR Global Sequence
We have been trying to load our existing database that contains local sequences into a BDR-based database with global sequences. So, we update our schema in the new database to USING bdr for all sequences. We make certain all tables have a primary key. Then we dump our data from our existing database.
We dump our existing database, including schema. We update the existing local sequences to USING bdr and remove the other initialization parameters. Then we must remove all setval’s that occur after the COPY statements; because, BDR does not like them. We can load this data; but, the global sequences are all initialized to 1 on our primary database and 15000 on our DR database.
We have went so far as to write a program that selects the max(id) on each table; then, it calls nextval until it reaches a value greater to or equal the max(id). This has to be run on all BDR nodes. Needless to say, this is slow and is a bad solution.
There just doesn’t seem to be a good way to accomplish this operation, at least not a documented way I have been able to find.
On 01/04/2015 12:14 AM, John Casey wrote: > We have been trying to load our existing database that contains local > sequences into a BDR-based database with global sequences. [snip] > There just doesn’t seem to be a good way to accomplish this operation, > at least not a documented way I have been able to find. I think you want default_seqam = bdr . https://wiki.postgresql.org/wiki/BDR_Global_Sequences#Global_sequences_created_by_default You can pass this to psql or pg_restore using a PGOPTIONS setting or an 'options=' entry in a connection dsn, e.g. pg_restore -d "dbname=mydb options='-c default_sequenceam=bdr'" dumpfile -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services