Handy commands when working with vPostgres

Copy Table to CSV

copy vpx_vm to '/tmp/vpx_vm' DELIMITER ',' CSV;

Copy table from CSV

copy table FROM '/tmp/table.csv' DELIMITER ',' CSV;

list Top tables by size:

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

postgres password:

root@is-dhcp34-161 [ / ]# cat ~/.pgpass

localhost:5432:replication:replicator:*v&w1pTkmZY}Q2<z
127.0.0.1:5432:replication:replicator:*v&w1pTkmZY}Q2<z
/var/run/vpostgres:5432:replication:replicator:*v&w1pTkmZY}Q2<z
localhost:5432:postgres:postgres:_ouG|OZ4NUwna0fB
127.0.0.1:5432:postgres:postgres:_ouG|OZ4NUwna0fB
localhost:5432:VCDB:postgres:_ouG|OZ4NUwna0fB
127.0.0.1:5432:VCDB:postgres:_ouG|OZ4NUwna0fB
/var/run/vpostgres:5432:VCDB:postgres:_ouG|OZ4NUwna0fB
/var/run/vpostgres:5432:postgres:postgres:_ouG|OZ4NUwna0f

Rename table

ALTER TABLE tablename RENAME TO new_table;

Database backup/Dump using pg_dump

pg_dump  VCDB -U postgres > /tmp/dump

Database backup (excluding a specific corrupted table)

pg_dump  VCDB -U postgres  -T vpx_host > /tmp/dump.excluting.currupt.table

Note: T: Exclude table and grab the rest.
      t: backup specific table only.

Determining broken tables(pg toast)

 for ((i=0; i<"668"; i++ )); do /opt/vmware/vpostgres/current/bin/psql -U "postgres" "VCDB" -c "SELECT * FROM VPX_VM LIMIT 1 offset $i" >/dev/null || echo $i; done

Note: Replace 668 with the highest table count on your setup

Starting postgres in single user mode:

su - postgres –c postgres --single -D /storage/db/vpostgres

use below to fix checksum on start 
su - postgres –c /path_to_postgres/postgres --single -D /storage/db/vpostgres -c fix_block_checksum="1663/1636/1694/0/978"

Note: Postgres will only start as the user postgres. ignoring the su - postgres will likely cause the service to fail

Reset WAL (Write Ahead logs)

pg_resetxlog /storage/db/vpostgres

Error “MultiXactId has not been created yet — apparent wraparound”

VACUUM FREEZE table_name; 

Manual Recovery of corrupt records from a postgres table.

A bash while loop that creates an SQL file to export every row individually into individual files. The rows with corrupt records are skipped.
you will need a unique readable collum. For the below example I have exported surr_id from vpx_text_array as /tmp/SURR_KEY_vpx_text_array and use a while loop to create individual lines for export on the .SQL file

while read p; do  echo "copy (select * from vpx_text_array where surr_key=$p) to '/db/$p' delimiter ',' csv;" >> /db/out.sql; done < /tmp/SURR_KEY_vpx_text_array

Use psql commandline to invoke the SQL script

/opt/vmware/vpostgres/9.4/bin/psql -U postgres -d VCDB -a -f /db/out.sql >> /db.export.err
psql.bin:/db/out.sql:153: ERROR:  missing chunk number 0 for toast value 66095 in pg_toast_19389  <----currupt records. TBR from other tables
psql.bin:/db/out.sql:190: ERROR:  missing chunk number 0 for toast value 66096 in pg_toast_19389  <----currupt records. TBR from other tables

Query to list top events from the events DB.

SELECT COUNT(EVENT_ID) AS NUMEVENTS, EVENT_TYPE, USERNAME FROM VPXV_EVENT_ALL GROUP BY EVENT_TYPE, USERNAME ORDER BY NUMEVENTS DESC LIMIT 10; 
 

DB schema paths

VCDB: 
VUMDB: /usr/lib/vmware-updatemgr/share/VCI_base_postgresql.sql