Handy commands when working with vPostgres

Copy Tables to CSV

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

Copy tables from CSVto table

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

Leave a comment

Your email address will not be published. Required fields are marked *