Hello,
We are looking to distribute postgres databases to our customers along with our
application. We are currently evaluating postgres version 8.4.4. The database
can be of size 25 gb (compressed files fits in few dvds, the product is
distributed on dvds). The pg_restore of this database takes several hours on
the
low end machines running windows os. The pg_restore is run during our product
install, and the current install time projection is not acceptable. Our
customers can purchase different databases over a period of time, and the
application makes transactional updates to the databases after installation.
Hence, copying the entire data folder instead of using the pg_restore is not an
option, as the transactional updates will be lost.
I have read the documentation and the few posts available that discourages file
copy based restore of individual databases, but, I have found a way to do this.
I would appreciate if the experts can read and advise if the approach will
work,
given our environment and usage boundaries.
Master Postgres instance (this is where we create the data, we have complete
control of this environment):
1. Create the database and populate data.
2. Set vacuum_freeze_table_age to 0 in the postgresql.conf
3. Run vacuum full - this will reset the row xid to the FrozenXid
4. Shutdown postgres and take a copy of the files for the given database.
In the deploy instance at the customer site:
1. Create the new database.
2. Shutdown postgres instance and copy the database files created in the master
instance to the database specific folder.
3. Start postgres instance.
We don't use table row oids. If the cluster wide oid collides with the oid in
the copied database files during subsequent ddl operations, postgres resolves
this by skipping to the next available oid. There will be a delay to find the
next available oid, which is acceptable in our case, as the ddl operations at
the customer site are rare. And, the vacuum full with vacuum_freeze_table_age
set to 0 on the master instance takes care of the xmin, allowing transactions
to
be visible, and for further transactions at the customer site to continue
without colliding.
I have tested this and it works, and I am continuing to test it more. I would
like for validation of this idea from the experts and the community to make
sure
I haven't overlooked something obvious that might cause issues.
Thank you,
Srini
