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|OZ4NUwna0fRename 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 setupStarting 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 failReset WAL (Write Ahead logs)
pg_resetxlog /storage/db/vpostgresError “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 
