Are there any quick ways to move PostgreSQL database between clusters on the same server?

user_13588942 0
Viewed 1474

We have two big databases (200GB and 330GB) in our "9.6 main" PostgreSQL cluster. What if we create another cluster (instance) on the same server, is there any way to quickly move database files to new cluster's folder?

Without using pg_dump and pg_restore, with minimum downtime.

We want to be able to replicate the 200GB database to another server without pumping all 530GB of data.

postgresql
3 Answers

Databases aren't portable, so the only way to move them to another cluster is to use pg_dump (which I'm aware you want to avoid), or use logical replication to copy it to another cluster. You would just need to set wal_level to 'logical' in postgresql.conf, and create a publication that included all tables.

CREATE PUBLICATION my_pub FOR ALL TABLES;

Then, on your new cluster, you'd create a subscription:

CREATE SUBSCRIPTION my_sub 
  CONNECTION 'host=172.100.100.1 port=5432 dbname=postgres'
  PUBLICATION my_pub;

More information on this is available in the PostgreSQL documentation: https://www.postgresql.org/docs/current/logical-replication.html

user_121961760

TL;DR: no.

PosgreSQL itself does not allow to move all data files from a single database from one source PG cluster to another target PG cluster, whether the cluster runs on the same machine or on another machine. To this respect it is less flexible than Oracle transportable tablespaces or SQL Server attach/detach database commands for example.

The usual way to clone a PG cluster is to use streaming physical replication to build a physical standby cluster of all databases but this requires to backup and restore all databases with pg_basebackup (physical backup): it can be slow depending on the databases size but once the standby cluster is synchronized it should be really fast to failover to standby cluster by promoting it; miminal downtime is possible. After promotion you can drop the database not needed.

However it may be possible to use storage snaphots to copy quickly all data files from one source cluster to another cluster (and then drop the database not needed in the target cluster). But I have not practiced it and it does not seem to be really used (except maybe in some managed services in the cloud).

(PG cluster means PG instance).

user_107268500

If You would like to avoid pg_dump/pg_restore, than use:

  1. logical replication (enables to replicate only desired databases)
  2. streaming replication via replication slot (moving the whole cluster to another and then drop undesired databases)

While 1. option is described above, I will briefly describe the 2.:

a) create role with replication privileges on master (cluster I want to copy from)

master# psql> CREATE USER replikator WITH REPLICATION ENCRYPTED PASSWORD 'replikator123';

b) log to slave cluster and switch to postgres user. Stop postgresql instance and delete DB data files. Then You will initiate replication from slave (watch versions and dirs!):

pg_basebackup -h MASTER_IP -U replikator -D /var/lib/pgsql/11/data -r 50M -R –waldir /var/lib/pgwal/11/pg_wal -X stream -c fast -C -S master1_to_slave1 -v -P

What this command do? It connects to master with replikator credentials and start pg_basebackup via slot that will be created. There is bandwith throttling as well (50M) as other options... Right after the basebackup slave will start streaming replication and You've got failsafe replication.

c) Then when You want, promote slave to be standalone and delete undesired databases:

rm -f /varlib/pgsql/11/data/recovery.conf
systemctl restart postgresql11.service
user_59987820
Related