Difference between revisions of "Moving the PostgreSQL Database to a New Location in Linux"

From Observer GigaFlow Support | VIAVI Solutions Inc.
Jump to: navigation, search
 
(One intermediate revision by one user not shown)
Line 1: Line 1:
[[Category:Databasing Notes]]
+
[[Category:Database]]
[[Category:Technical Notes]]
+
  
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.
+
Change /data to the folder you want to use
  
  # TYPE  DATABASE        USER            ADDRESS                METHOD
+
  mkdir /data/gigaflow
# "local" is for Unix domain socket connections only
+
  systemctl stop gigaflow
host all myipfix 127.0.0.1/32 md5
+
  systemctl stop postgresql-11
'''host all postgres 172.21.40.131/32 trust'''
+
  echo "rsync -av /var/lib/pgsql/11/data/ /data/gigaflow"
  local  all            all                                    md5
+
  rsync -av /var/lib/pgsql/11/data/ /data/gigaflow
  # IPv4 local connections:
+
echo "mv /var/lib/pgsql/11/data /var/lib/pgsql/11/data_old"
  host    all            all            127.0.0.1/32            md5
+
  mv /var/lib/pgsql/11/data /var/lib/pgsql/11/data_old
# IPv6 local connections:
+
  echo "ln -s /data/gigaflow /var/lib/pgsql/11/data"
host    all            all            ::1/128                md5
+
ln -s /data/gigaflow /var/lib/pgsql/11/data
 
+
chown postgres:postgres /var/lib/pgsql/11/data
On the target server, stop the existing Anuview Flow service and restart postgres once changed.
+
  chmod 0700 /var/lib/pgsql/11/data
/etc/init.d/rosd stop
+
systemctl start postgresql-11
/etc/init.d/postgresql-95 restart
+
  systemctl start gigaflow
 
+
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
+

Latest revision as of 11:45, 3 February 2022


Change /data to the folder you want to use

mkdir /data/gigaflow
systemctl stop gigaflow
systemctl stop postgresql-11
echo "rsync -av /var/lib/pgsql/11/data/ /data/gigaflow"
rsync -av /var/lib/pgsql/11/data/ /data/gigaflow
echo "mv /var/lib/pgsql/11/data /var/lib/pgsql/11/data_old"
mv /var/lib/pgsql/11/data /var/lib/pgsql/11/data_old
echo "ln -s /data/gigaflow /var/lib/pgsql/11/data"
ln -s /data/gigaflow /var/lib/pgsql/11/data
chown postgres:postgres /var/lib/pgsql/11/data
chmod 0700 /var/lib/pgsql/11/data
systemctl start postgresql-11
systemctl start gigaflow