recreate postgres instance is VCSA

Dump out VCDB the database to

/opt/vmware/vpostgres/current/bin/pg_dump -d VCDB -U postgres -Fp -c > /storage/updatemgr/VCDBackUp

Drop the database.

/opt/vmware/vpostgres/current/bin/dropdb -U postgres VCDB

dumpout the instance
su -m -c “/opt/vmware/vpostgres/current/bin/pg_dumpall -c -f /storage/updatemgr/GSS_dumpall.sql” postgres

Backup the data directory
cp /storage/db/vpostgres /storage/db/vpostgres_bk

Remove all dependencies including seat data

cd /storage/db/vpostgres
rm -rf *

rm /storage/seat/vpostgres/tasktblsp/*
rm /storage/seat/vpostgres/alarmtblsp/*
rm /storage/seat/vpostgres/hs2tblsp/*
rm /storage/seat/vpostgres/hs3tblsp/*
rm /storage/seat/vpostgres/hs1tblsp/*
rm /storage/seat/vpostgres/hs4tblsp/*
rm /storage/seat/vpostgres/eventtblsp/*

Create and start new instance

su – vpostgres -c “/opt/vmware/vpostgres/current/bin/initdb -D /storage/db/vpostgres”
su – vpostgres -c “/opt/vmware/vpostgres/current/bin/pg_ctl -D /storage/db/vpostgres start”

Create postgres user
/opt/vmware/vpostgres/current/bin/createuser -s postgres

Import the instance
/opt/vmware/vpostgres/current/bin/pg_dumpall -U postgres -c -f /storage/updatemgr/ddumps/GSS_dumpall.sql

Create VCDB and user.
/opt/vmware/vpostgres/current/bin/createdb -U postgres VCDB
Connect to the database:
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB
psql.bin (9.4.12 (VMware Postgres 9.4.12.0-5574246 release))
Type “help” for help.

Run the following queries::
create schema vc;
ALTER USER VC SET search_path to vc,pg_catalog;
ALTER USER postgres SET search_path to vc,pg_catalog;
ALTER USER vc WITH PASSWORD ‘d#Bu|AWL+x&{Y3j5’; — get this from /etc/vmware-vpx/vcdb.properties

ALTER DATABASE “VCDB” OWNER TO vc;

VCDB=> \q

We will now restore the previosuly backed up database:

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres -f /storage/updatemgr/VCDBackUp >/tmp/output_backup.txt 2>&1
Wait until it finishes.
Check the log on /tmp/output_backup.txt

Once it completes test by querying a table:

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U vc
select * from vpx_entity; —confirm that rows are returned.

VCDB=> \q

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s