Content library hack(DB)

Posted on by Nithin Titta

VMware Content library, A unique way to make VM templates/ISO’s available across multiple vCenter’s. However, It does not handle Datacenter segregation very well when storing the contents on NFS/VMFS.

A subscribed library would need dedicated storage space and would be pointless in my setup as I had presented the NFS volume to all host.

So what seems to be the problem?

I have an NFS volume presented across all the host across several Data center. Although the NFS UUID and the name of the datastore are the same (ISO), on the vCenter database, this is stored with a different datacenter ID as they are segregated by the datacenter object. 


  id  |      name      | datacenter_id
 -----+----------------+---------------
  157 | Template       |            77
  158 | SlowBro_400    |            77
  159 | ISO            |            77
  160 | SharedLUN      |            77
  161 | 10.154         |            77
  156 | template       |            77
   13 | SlowBro_legasy |             2
   14 | 10.128         |             2
   91 | ISO            |             2
   16 | Template       |             2
   92 | is-tse-d129_1  |             2
   12 | SlowBro_400    |             2

VCDB=# select * from vpx_entity where id=77;
  id |     name     | type_id | parent_id
 ----+--------------+---------+-----------
  77 | BLR          |       8 |         1
 (1 row)


VCDB=# select * from vpx_entity where id=2;
  id |    name    | type_id | parent_id
 ----+------------+---------+-----------
   2 | HYD        |       8 |         1
 (1 row)

With this configuration, When I had created a content library, as per the db, this was only referencing to once of the site datastore, IE: we only see iso (id 159) from BLR (id 77) datacenter.

What this means is The content library objects can only be deployed to one of the datacenter rather than being able to deploy them across all datacenter’s on the vCenter. 

VCDB=# select * from cl_storage;
                  id                  |                          storageuri                          |   type
--------------------------------------+--------------------------------------------------------------+-----------
 a0018db8-f630-4c04-b0f1-30c900ad691c | Datastore:datastore-159:481639ff-d88d-4622-8872-ec6856e6b157  | Datastore
 bf8e8dcb-5b28-4b03-863e-89308bc8c501 | Datastore:datastore-157:481639ff-d88d-4622-8872-ec6856e6b157 | Datastore


The above table references:
VCDB=# select * from cl_library_storage;
              library_id              |              storage_id
--------------------------------------+--------------------------------------
 3336e2ad-8166-4e6a-850d-a9d81c41ba01 | a0018db8-f630-4c04-b0f1-30c900ad691c
 946cea65-5cd0-41e0-83ab-17259f690ce1 | bf8e8dcb-5b28-4b03-863e-89308bc8c501

So.. Logically, If I add the other ID for ISO on this table cl_storage and create a referencing record in cl_library_storage then we should be able to use the same content library across all datacenter.

The ID from the above must be unique and must match the two tables. I added the below records (i added another record by incrementing one of the values after the co-relating table. 

after change
VCDB=# select * from cl_storage;
                  id                  |                          storageuri                          |   type
--------------------------------------+--------------------------------------------------------------+-----------
 a0018db8-f630-4c04-b0f1-30c900ad691c | Datastore:datastore-159:481639ff-d88d-4622-8872-ec6856e6b157  | Datastore
 bf8e8dcb-5b28-4b03-863e-89308bc8c501 | Datastore:datastore-157:481639ff-d88d-4622-8872-ec6856e6b157 | Datastore
 bf8e8dcb-5b28-4b03-863e-89308bc8c502 | Datastore:datastore-11:481639ff-d88d-4622-8872-ec6856e6b157  | Datastore

VCDB=# select * from cl_library_storage;
              library_id              |              storage_id
--------------------------------------+--------------------------------------
 3336e2ad-8166-4e6a-850d-a9d81c41ba01 | a0018db8-f630-4c04-b0f1-30c900ad691c
 946cea65-5cd0-41e0-83ab-17259f690ce1 | bf8e8dcb-5b28-4b03-863e-89308bc8c501
 946cea65-5cd0-41e0-83ab-17259f690ce1 | bf8e8dcb-5b28-4b03-863e-89308bc8c502

after adding the above records, I am now able to deploy VM’s from the content library across Datacenters.

content library DB schema can be found here:

/usr/lib/vmware-content-library/support/scripts/db/PostgreSQL/cls_unified/cls60.sql

Handy commands when working with vPostgres

Posted on by Nithin Titta

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