Need some help on PG database transfer

Started by Steelesover 15 years ago2 messagesgeneral
Jump to latest
#1Steeles
steeles@gmail.com

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.

#2Scott Marlowe
scott.marlowe@gmail.com
In reply to: Steeles (#1)
Re: Need some help on PG database transfer

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.