DBA Hub

📋Steps in this guide1/6

How to clone a postgres database to remote server - DBACLASS DBACLASS

In the below tutorial , we will explain how to clone a POSTGRES database to remote postgres server. pg_dump -C -d dbatest

postgresql configurationintermediate
by PostgreSQL
12 views
1

Overview

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
2

Section 2

remote postgres server details: host -> dbaclass-remote port -> 5444 superuser -> enterprisedb exiting_db -> postgres
3

Section 3

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
local$<span style="color: #ff0000;"><strong> psql --host dbaclass-remote -p 5444 -d postgres</strong></span>
psql (12.3.4)
Type "help" for help.

postgres=# <span style="color: #ff0000;"><strong>\conninfo</strong></span>
You are connected to database "postgres" as user "enterprisedb" on host "dbaclass-remote" at port "5444".
postgres=#


postgres=# <span style="color: #ff0000;"><strong>\list</strong></span>
                                           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)
4

Section 4

2. Check the objects present in local postgres db:[ON LOCAL] 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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
dbatest=# <span style="color: #ff0000;"><strong>\dt</strong></span>
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)
5

Section 5

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. 5. Check whether the database and its objects were replicated or not:[ on remote server]

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-bash-4.2$ <span style="color: #ff0000;"><strong>pg_dump -C -d dbatest -U enterprisedb | psql -h dbaclass-remote -U enterprisedb -d postgres</strong></span>

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
6

Section 6

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
=# \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)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!