0

I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records).

To solve this, I run the following (as pseudocode):

last=1
for db in databases:
   ALTER TABLE table1 DROP CONSTRAINT table1_pkey
   CREATE SEQUENCE temp_seq START last
   UPDATE table1 SET table1_pk = nextval('temp_seq')
   ALTER TABLE table1 ADD PRIMARY KEY table1_pk
   last = nextval('temp_seq')
   DROP SEQUENCE temp_seq

This goes through all the databases and resets their indices starting from 1. First database has indices 1-50, second 51-125, third 126-223 and so on.. After this I dump and restore them to one database and there is no overlap anymore.

Everything works well, other than the index reset part is extremely slow with bigger databases (many GBs of data). I found out that postgres is running VACUUM ANALYZE after every UPDATE table1 SET table1_pk = nextval('temp_seq'). This makes everything even slower than before. According to my understanding, this is done to prevent transaction ID wraparound? From the PostgreSQL docs: https://www.postgresql.org/docs/9.3/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction's XID is "in the future" and should not be visible to the current transaction. But since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss. (Actually the data is still there, but that's cold comfort if you cannot get at it.) To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions.

Is this the correct reason? Is there a faster way to do primary key reset? Or to tune the vacuuming parameters without any data loss?

1

Are these databases in the same Postgres Cluster?

If so, you might consider a Foreign Data Wrapper, to create a "connection" to each source database and insert the data directly into the new table:

CREATE EXTENSION POSTGRES_FDW ;

CREATE SERVER foreign_db 
FOREIGN DATA WRAPPER POSTGRES_FDW 
OPTIONS 
( HOST       'host_name' 
, DBNAME     'database_name' 
, UPDATABLE  'false' 
);

CREATE USER MAPPING FOR fred 
SERVER foreign_db 
OPTIONS ( USER 'fred', PASSWORD 'password' );

CREATE FOREIGN TABLE foreign_old_table 
(
   ... 
)
SERVER foreign_db 
OPTIONS ( TABLE_NAME 'old_table' );

Then; finally

INSERT INTO new_table ( everything, except, pk ) 
SELECT ( everything, except, id ) 
FROM foreign_old_table ; 
  • Sorry, they are not in the same cluster. I have to use a different approach. – Asgmch Jun 13 at 11:40
0

Vacuum Analyze isn't just to prevent Transaction Wraparound. It also updates Table Statistics, works out if Indexes need rebuilding and other, useful stuff.

XID's are not related to any table's Primary Key id's.
Indeed, you can (and probably should) add all the rows from one table into another as part of just one Transaction, with just one XID. The Analyze is probably being run because you're updating every single row in the table and that constitutes a change "large" enough for Analyze to stick its nose in and have another rummage around that table.

Could you create Views in the source databases that "alter" the Primary Key value "on the fly", pg_dump that and load that into your single database?

create view old_table_dump as 
select id + base_value, everything, expect, id 
from old_table ; 
  • That might work. The problem is my databases have dozens of tables and I have a hundred databases to merge. Creating views for them automatically is hard and takes too long manually. – Asgmch Jun 17 at 14:08
  • Moving this data is going to require downtime. That can be negotiated on an individual client basis so that you don't have one giant hit where you have to do a hundred databases all as once. Actually, you /can/ use the foreign data wrapper /between/ clusters (as long as they're connectable / not fire-walled, etc.) - look at the HOST and DBNAME entries on the create server command. Being able to 'insert .. select' the data straight into the new tables should be a big improvement. – Phill W. Jun 17 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.