vPostgres Database useful commands

• Command to connect vPostgres :

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

• We can also connect with the vPostgres database using pgAdmin tool :

• vPostgres configuration related files :-

/storage/db/vpostgres/pg_hba.conf – This config file stores the information about the users that can connect to the database. It also stores the info about the information about from where users can connect from, and also what database they will see once connected.

/storage/db/vpostgres/postgresql.conf – This conf file has all runtime settings. The conf file is re-read whenever the main server process receives a SIGHUP signal. Please don’t modify this file.

/storage/db/vpostgres/pg_ident.conf – This has the user name mappings. Postgres allows external authentication users to be mapped to database users.

• Version file : /storage/db/vpostgres/PG_VERSION

• ODBC DSN definition file : /etc/odbc.ini

• To run pg_dump for backup :

/opt/vmware/vpostgres/current/bin/pg_dump -a -Fc –disable-triggers -b -U postgres VCDB -f /storage/core/bkp

• Restore vPostgres using pg_restore :

root@vcsa1 [ /opt/vmware/vpostgres/current/bin ]# ./pg_restore -a -Fc –disable-triggers -U postgres –dbname=vcdbbackup

• ODBC DSN definition file : /etc/odbc.ini

• pg_dumpall utility is use to take backup of entire database cluster. Pg_dump is use to take complete backup of only the database.

• psql command can be used to copy a table content. Note that psql cmd will not copy the schema. It copies only the content of the table.

Ex. root@vcsa1 [ ~ ]#  /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

VCDB=# copy vpx_version to ‘/tmp/vpxversion.out’;

COPY 1

OR

root@vcsa1 [ ~ ]# /opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c “copy vpx_version to ‘/tmp/vpxversion1.out’;”

COPY 1

• pg_dump to take backup of a table :

pg_dump -U postgres -d dbname -t tablename > /tmp/backup

• Postgres Utilities :-
• Pg_ctl (PG control) : This is a  utility for initializing the postgres DB cluster, starting, stopping, or restarting database server.
• Pg_controldata : This is used to print information initialized during initdb, such as catalog version, WAL and checkpoint processing.
• Pg_top : This is used to check the performance of the postgres instance. Like CPU usage etc.
• Pg_resetxlog : This utility is used to clear the WAL (write-ahead log) and optionally reset some other control information stored in the pg_control.

WAL files are the transaction files which still has not been written to the data file. After running this command the database might contain inconsistent data due to partially committed transactions. This command must not be used when the postgres service is running.

• Pg_basebackup : This is used to take backup of the complete database cluster.
• VACUUM : Vacuum (without full) command reclaims the space from database and make it available for re-use to the database.

Sometimes when the data/logs is deleted from the database, the space doesn’t release till the time we do vacuum.

Vacuum full can reclaim more space, but it takes much longer and exclusively locks the table.

VACUUM ANALYZE performs a vacuum and then an ANALYZE for each selected table.

We can run vacuum command in verbose level :

AutoVacuum : It is used to automate the execution of VACUUM and ANALYZE commands.

# ps -awx | grep -i postgres | grep vacuum

 4659 ?        Ss     0:00 postgres: autovacuum launcher process

• More useful links :

Backup and restore vPostgres database on Windows and Appliance vCenter : https://kb.vmware.com/s/article/2091961