pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedcopy databases from two differend backups to one cluster
3+ messages / 3 participants
[nested] [flat]
* copy databases from two differend backups to one cluster
@ 2022-11-18 12:33 Joseph Kennedy <joseph.kennedy.486@gmail.com>
2022-11-18 15:31 ` Re: copy databases from two differend backups to one cluster Ron <ronljohnsonjr@gmail.com>
2022-11-18 16:18 ` Re: copy databases from two differend backups to one cluster Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 3+ messages in thread
From: Joseph Kennedy @ 2022-11-18 12:33 UTC (permalink / raw)
To: pgsql-general
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 ?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: copy databases from two differend backups to one cluster
2022-11-18 12:33 copy databases from two differend backups to one cluster Joseph Kennedy <joseph.kennedy.486@gmail.com>
@ 2022-11-18 15:31 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron @ 2022-11-18 15:31 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: copy databases from two differend backups to one cluster
2022-11-18 12:33 copy databases from two differend backups to one cluster Joseph Kennedy <joseph.kennedy.486@gmail.com>
@ 2022-11-18 16:18 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2022-11-18 16:18 UTC (permalink / raw)
To: Joseph Kennedy <joseph.kennedy.486@gmail.com>; pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-11-18 16:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-18 12:33 copy databases from two differend backups to one cluster Joseph Kennedy <joseph.kennedy.486@gmail.com>
2022-11-18 15:31 ` Ron <ronljohnsonjr@gmail.com>
2022-11-18 16:18 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox