In the below tutorial , we will explain how to clone a  POSTGRES database to remote server.

source postgres details:

host      -> dbaclass-local
db_name   -> dbatest — > This database need to be migrated.
port      -> 5444
superuser -> enterprisedb

remote postgres server details:

host       -> dbaclass-remote
port       -> 5444
superuser  -> enterprisedb
exiting_db -> postgres

1. Test whether you are able to connect from local to remote existing database.[on local]

local$ psql --host dbaclass-remote -p 5444 -d postgres
psql (12.3.4)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" on host "dbaclass-remote" at port "5444".
postgres=#


postgres=# \list
                                           List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    | ICU |       Access privileges
-----------+--------------+----------+-------------+-------------+-----+-------------------------------
 postgres  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |
 template0 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
 template1 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
(5 rows)

2. Check the objects present in local postgres db:[ON LOCAL]

dbatest=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------------
public | test1 | table | enterprisedb
public | test2 | table | enterprisedb
public | test3 | table | enterprisedb
public | test4 | table | enterprisedb
(4 rows)

2. Now run the clone script using pg_dump command:[RUN ON LOCAL SERVER]

syntax – > pg_dump -C -d database – U username | psql -h remotehost -U username -d remote_database

Here remote_database is an existing database in the remote postgres cluster, so that this command can connect to an existing database and run the create database command.

-bash-4.2$ pg_dump -C -d dbatest -U enterprisedb | psql -h dbaclass-remote -U enterprisedb -d postgres

SET
SET
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "dbatest" as user "enterprisedb".
SET
SET
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 12544
COPY 160
COPY 12544
COPY 12544

5. Check whether the database and its objects were replicated or not:[ on remote server]

=# \list
                                           List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    | ICU |       Access privileges
-----------+--------------+----------+-------------+-------------+-----+-------------------------------
 dbatest   | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |
 postgres  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |
 template0 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
 template1 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
(5 rows)


dbatest=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------------
public | test1 | table | enterprisedb
public | test2 | table | enterprisedb
public | test3 | table | enterprisedb
public | test4 | table | enterprisedb
(4 rows)

We can see the database and its objects were created in the remote machine.