Thread: Need some help on PG database transfer
Hi all,
I am doing some tests on the file level copying for one of database in windows platform. Here is what I want to achieve.
copy files within PG tablespace folder where PG database resides to target machine. I want to attache the copied data to target PG database.
What I have done is
1. Database: ABCD, OID: 12345, tablespace: e:\pg_data\, obviously, e:\pg_data has a subfolder named 12345.
2. create database in target, named 2222, and it will create its own OID, for example, 16333, the folder 16333 will reside under BASE folder.
3. then I stop PG service, delete all files under ..\16333\*.*,
4. copy all files from e:\pg_data\12345\*.* to the folder in target server, ..\16333\.
5. start PG service in target machine. it looks like it pickup most of tables that are from its source database, ABCD.
But, It is missing tables and functions, compared to sources.
Is it doable for replicating data like that?
PG tablespace, does it contain all the data/tables in its table space folder?
Please comment.
Thanks.
I am doing some tests on the file level copying for one of database in windows platform. Here is what I want to achieve.
copy files within PG tablespace folder where PG database resides to target machine. I want to attache the copied data to target PG database.
What I have done is
1. Database: ABCD, OID: 12345, tablespace: e:\pg_data\, obviously, e:\pg_data has a subfolder named 12345.
2. create database in target, named 2222, and it will create its own OID, for example, 16333, the folder 16333 will reside under BASE folder.
3. then I stop PG service, delete all files under ..\16333\*.*,
4. copy all files from e:\pg_data\12345\*.* to the folder in target server, ..\16333\.
5. start PG service in target machine. it looks like it pickup most of tables that are from its source database, ABCD.
But, It is missing tables and functions, compared to sources.
Is it doable for replicating data like that?
PG tablespace, does it contain all the data/tables in its table space folder?
Please comment.
Thanks.
On Thu, Jul 22, 2010 at 2:08 PM, Steeles <steeles@gmail.com> wrote: > Hi all, > > I am doing some tests on the file level copying for one of database in > windows platform. Here is what I want to achieve. > > copy files within PG tablespace folder where PG database resides to target > machine. I want to attache the copied data to target PG database. That won't work. You have to pg_dump out the tables / tablespace and then psql or pg_restore it to the other db.