Monday, August 15

Database/schema duplication in Postgres

In Oracle it is extremely simply to replicate a schema to another. Using an export and doing the import using the fromuser/touser options it is done within minutes without hassle with permissions or other issues.
So how do I do that in Postgres?
Here is the method I currently use, way to many steps IMO.
postgres@server77[~]$ pg_dump -O database > database_dump.sql
postgres@server77[~]$ psql template1 postgres
Welcome to psql 8.0.3, the PostgreSQL interactive terminal. [...]
template1=> create user bob password 'bobby123';
CREATE USER
template1=> grant all on tablespace data1 to bob;
GRANT
template1=> alter user bob set default_tablespace to 'data1';
ALTER USER
template1=> alter user bob set search_path to schema1,
schema2, public;

NOTICE: schema "schema1" does not exist
NOTICE: schema "schema2" does not exist
ALTER USER
template1=> create database newdb owner=bob tablespace=data1
template1-> encoding='unicode' template=template1;
CREATE DATABASE
template1=> \connect newdb bob
You are now connected to database 'newdb' as user 'bob'.
newdb=> \i database_dump.sql
[ lots of output from data execution ]
newdb=> \d
[ list of relations, make sure all your
objects are present ]

newdb=> ^D

I'm welcome to suggestions on how to do this easier and faster. Our developers love to have test database to break and play with.

Another thing is that I've seen some problems with schema entities from Java, even tho the user search_path is set correctly it's not possible to reference a table.
This is even more likely to happened when a schema has been renamed. Our Java guru Andres (hey dude!) have informed me about this a few times. I'll see if I can find something in PG bugzilla or I'll submit a new report to the development team.

Over and out.

No comments: