Thread: copy databases from two differend backups to one cluster

On 11/18/22 06:33, Joseph Kennedy wrote:

I would like to copy databases from two different pg_basebackup backups to one lab environment database cluster on another server. Is it possible ?

It's a good idea to do it in that way.

Databases has your oid in pg_basebackup we can find it id in base directory for example:

 base/

1

125859

14042

14043

2315410

2321461

2321462

2321463

Database OID (and directory name) we can find by :

SELECT oid from pg_database where datname = 'database name';

 Steps:

create empty databases on destination server (database oid they will be probably different then on source databases)

stop Postgresql

cleanup destination directories eg. 2315410, 2321461, 2321462, 2321463

copy eg. content from pg_bassebackup01 base/(some OID) to 2315410 and 2321461

copy eg. content from pg_bassebackup02 base/(some OID) to 2321462 and 2321463

start postgresql


Is it a good or  very bad idea ?

Why can't you do pg_dump/pg_restore?

On 11/18/22 04:33, Joseph Kennedy wrote:
> I would like to copy databases from two different pg_basebackup backups 
> to one lab environment database cluster on another server. Is it possible ?
> 
> It's a good idea to do it in that way.
> 
> Databases has your oid in pg_basebackup we can find it id in base 
> directory for example:

> Is it a good or  very bad idea ?

Bad idea.

For why see:

https://www.postgresql.org/docs/current/storage-file-layout.html

and

https://www.postgresql.org/docs/current/storage-page-layout.html

There is more to a cluster then what is found in a given databases 
directory. You can't, at the file(binary) level, just rip a database out 
of one cluster and graft it into another.

If you want to do this then the options are:

1) As Ron suggested dump/restore.

2) Setting up logical replication.

> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com