Moving the PostgreSQL Database to a New Location in Linux

From Observer GigaFlow Support | VIAVI Solutions Inc.
Revision as of 14:43, 17 October 2016 by Kevin Wilkie (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

On the target server edit the pg_hba.conf file to allow the connection from the source (172.21.40.131 in this example) server.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
host all myipfix 127.0.0.1/32 md5
host all postgres 172.21.40.131/32 trust
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

On the target server, stop the existing Anuview Flow service and restart postgres once changed.

/etc/init.d/rosd stop
/etc/init.d/postgresql-95 restart

On the source server run the following: Drop the "myipfix" database on the target server

/opt/postgresql/pg95/bin/psql -h 172.21.40.132 -U postgres postgres -c "drop database myipfix"

If prompted. enter your postgres user password (default should be P0stgr3s_2ME) Output should be "DROP DATABASE" or "ERROR: database 'myipfix' does not exist"

On the source server, run the following command

/opt/postgresql/pg95/bin/pg_dump -C -h 127.0.0.1 -U postgres myipfix | /opt/postgresql/pg95/bin/psql -h 172.21.40.132 -U postgres

If prompted. enter your postgres user password (default should be P0stgr3s_2ME) If you see any errors, it could be that you haven't dropped the database on the target server yet.

On the source server, now run

/opt/postgresql/pg95/bin/psql -h 172.21.40.132 -U postgres myipfix -c "delete from serverparams where key like 'serverid'"

Output should be "DELETE 1"

On the target server edit the pg_hba.conf file and remove the previously added line allowing trusted access.

On target server, restart postgres then the Anuview Flow service

/etc/init.d/postgresql-95 restart
/etc/init.d/rosd start