Postgresql: The magical way of looking at row counts on a table in postgresql.

On volatile table, the row counts can be off so much between vacum. I have seen a case where the table simply not reflecting the real rows counts for weeks. 

Looking at pg_class. The vpx_event_arg table have had that counts for awhile. The reading from pg_class and pg_stat_all_tables are off too. I am not sure what is the right row counts for the table anymore.

select relname as TABLE_NAME,reltuples AS ROW_NUMBERS FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') and relname ='vpx_event_arg' AND relkind='r'
ORDER BY reltuples DESC;

  table_name   | row_numbers

---------------+-------------
vpx_event_arg |      215304
(1 row)


VCDB=# select relname, reltuples from pg_class where relname ='vpx_event_arg';

    relname    | reltuples
---------------+-----------
vpx_event_arg |    215304
(1 row)

Taking the insert and minus the delete in hoping to find the stale data. 

VCDB=#  SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables where relname = 'vpx_event_arg';

    relname    | rowcount
---------------+----------
vpx_event_arg |     7569
(1 row)



VCDB=# SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables where relname = 'vpx_event_arg';
schemaname |    relname    | n_live_tup
------------+---------------+------------
vc         | vpx_event_arg |     214571
(1 row)

VCDB=#  select 214571 - 215304;
?column?
----------
     -733
(1 row)



http://www.postgresql.org/docs/9.3/static/catalog-pg-class.html

The document states that the reltuples will be updated after VACUUM and ANALYZE

VCDB=# analyze vpx_event_arg;
ANALYZE


select relname as TABLE_NAME,reltuples AS ROW_NUMBERS FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') and relname ='vpx_event_arg' AND relkind='r'
ORDER BY reltuples DESC;


VCDB=# select relname, reltuples from pg_class where relname ='vpx_event_arg';
    relname    | reltuples
---------------+-----------
vpx_event_arg |    221059
(1 row)

VCDB=# SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables where relname = 'vpx_event_arg';
    relname    | rowcount
---------------+----------
vpx_event_arg |     7569
(1 row)

VCDB=# SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables where relname = 'vpx_event_arg';
schemaname |    relname    | n_live_tup
------------+---------------+------------
vc         | vpx_event_arg |     221059
(1 row)

VCDB=# select 221059 - 221059;
?column?
----------
        0
(1 row)

VCDB=#

As  you can see the table has 733 off between pg_class and pg_stat_all_tables. The  n_tup_ins minus n_tup_del (7569)supposing-ly a pretty reliable reading but far off in my case. I am not sure which number I should trust here. 

However, I still recommend that, if a DBA would like to find out a fairly accurate table count, do an Analyze first.

Postgresql: could not fork autovacuum worker process: Cannot allocate memory

Problem statement

Postgresql crashes randomly. Increasing more memory do not helped much but only delaying the crashing.  There is no particularl activities that causing or caused to crash. It can be any DMLs or vacuum. Out of Memory was pretty consistent but do not always crashed the instance.

Errors from the logs

.................

      3 tm:2015-08-06 03:52:33.326 UTC db:VCDB pid:19666 STATEMENT:  UPDATE VPX_DVPORT SET 

      1 tm:2015-08-06 03:52:33.520 UTC db:VCDB pid:15745 LOG:  unexpected EOF on client connection
      1 tm:2015-08-06 03:52:33.524 UTC db:VCDB pid:8630 LOG:  could not receive data from client: Connection reset by peer
      2 tm:2015-08-06 03:52:33.524 UTC db:VCDB pid:8630 LOG:  unexpected EOF on 

..........................................

  could not receive data from client: Connection reset by peer
: could not fork autovacuum worker process: Cannot allocate memory
: could not fork autovacuum worker process: Cannot allocate memory

.......................................................................

ErrorContext: 8192 total in 1 blocks; 8160 free (4 chunks); 32 used
      1 tm:2015-08-06 03:53:27.838 UTC db:VCDB pid:1234 ERROR:  out of memory
      2 tm:2015-08-06 03:53:27.838 UTC db:VCDB pid:1234 DETAIL:  Failed on request of size 32.
      3 tm:2015-08-06 03:53:27.838 UTC db:VCDB pid:1234 CONTEXT:  SQL statement "SELECT 1 FROM ONLY "vpx"."vpx_vdevice_file_backing" x WHERE "backing_id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

Upon looking at the VM, I realized the swap was 0 even though the memory usage were very high. At such a high memory usage, I would expect swap to be used.

esxp-vc01:/var/log/vmware/vpx # free -m

             total       used       free     shared    buffers     cached

Mem:         24161      21849       2312          0        414       7797

-/+ buffers/cache:      13637      10524

Swap:            0          0          0

At that point, I suggested my customer to check with their administrator of why the Swap was at 0. Was it allocated? Was it turned on?

To get an overview of the swap, look at the fstab and swapon.

cat /etc/fstab

swapon -s

Conclusion

In ideal scenario, if swap is configured and turned on, it should have something similar as the following.

[root@Cloud3 ~]# swapon -s

Filename                                Type            Size    Used    Priority

/dev/mapper/VolGroup00-LogVol01         partition       2064376 0       -1

[root@Cloud3 ~]# free -m

             total       used       free     shared    buffers     cached

Mem:         16050      13949       2101          0        313      13160

-/+ buffers/cache:        475      15575

Swap:         2015          0       2015

[root@Cloud3 ~]# cat /etc/fstab|grep swap

/dev/VolGroup00/LogVol01 swap                    swap    defaults        0 0

[root@Cloud3 ~]# blkid|grep swap

/dev/mapper/VolGroup00-LogVol01: TYPE="swap"

/dev/VolGroup00/LogVol01: TYPE="swap"

Now that, the Postgresql: Cannot allocate memory, can be triggered by anything that causing the memory pool to exhaust. It basically saying, the box has ran out of memory.

Postgresql: Corruption: FATAL: invalid page header in block

VM crashed and took the postgresql with it. It failed to come back up when the VM restarted with the following error. There was no complete recent backup and unable to start the database. This does not leave a lot of room to wiggle around.

portgres postgres FATAL : the database system is starting up
portgres postgres FATAL : the database system is starting up
portgres postgres FATAL : the database system is starting up
       FATAL: invalid page header in block 903213 of relation pg_tblspc/16385/PG_9.0_201106101/16386/17694
       LOG: startup process (PID 27451) exited with exit code 1
       LOG: aborting startup due to startup process failure
       LOG: database system was interrupted while in recovery at 2015-07-20 21:00:01 UTC
       HINT: This is probably means that some data is corrupted and yo will have to use the last backup for recovery.
       LOG: database system was not properly shut down; automatic recovery in progress
       LOG: redo starts at 33E/D06C5FXX
       LOG: record with Zero length at 33E/D06C5FXX
       LOG: last completed transaction was at log time  ..........
 ... . .. .. . .. .

The file does exist but corrupted.

ls -as 17694

1349454 17694

Added zero_damaged_pages = on to postgresql.conf and was about to start up the database. Would suggest  to perform a VACUUM FULL and get a backup right after that.

Postgresql: Recovering a single table in Postgresql when the database is backup with text format

As the subject stated, recovering a single when database was backup with text format.

1 - open the text format backup file.
2 - search for the table. In this example, I am using a fake table (vpx_version) to simulate this. 
3 - copy and paste them to excel spreadsheet. Remember to have the header.
4- save the excel spreadsheet in "CSV" format on DOS.
5- move the csv file to the location of where you want to load the data into.

  

  

Note: the table structure must already exist for COPY to work, it does not create the table. If you dropped the table instead of truncated or delete without where clause, you will need to create the table manually. The HEADER flag is when you actually have a header if you do not, it might bomb out depending on the type of data you have defined at the base table.

vcdb=> select * from vpx_version;

ver_id |       version_value

--------+----------------------------

    600 | VirtualCenter Database 6.0

(1 row)

vcdb=> truncate table vpx_version;

TRUNCATE TABLE

vcdb=> select * from vpx_version;

ver_id | version_value

--------+---------------

(0 rows)

vcdb=> \COPY vpx_version from test.csv CSV HEADER;

vcdb=> select * from vpx_version;

ver_id |       version_value

--------+----------------------------

    600 | VirtualCenter Database 6.0

    601 | VirtualCenter Database 6.0

    602 | VirtualCenter Database 6.0

    603 | VirtualCenter Database 6.0

    604 | VirtualCenter Database 6.0

    605 | VirtualCenter Database 6.0

(6 rows)

If there are options, I will urge user to do backup of the database with pg_dump -Fc or "create table" .. "from select all".

##################################


Well that's all .. ... restoring my vpx_version table to original state ...


postgres=# \c vcdb vc
You are now connected to database "vcdb" as user "vc".
vcdb=> select * from vpx_version;
 ver_id |       version_value
--------+----------------------------
    600 | VirtualCenter Database 6.0
    601 | VirtualCenter Database 6.0
    602 | VirtualCenter Database 6.0
    603 | VirtualCenter Database 6.0
    604 | VirtualCenter Database 6.0
    605 | VirtualCenter Database 6.0
(6 rows)

vcdb=> truncate table vpx_version;
TRUNCATE TABLE

vcdb=> select * from vpx_version;
 ver_id | version_value
--------+---------------
(0 rows)


vcdb=>

postgres@localhost:~> pg_restore -U vc -d vcdb -a -t vpx_version vcdb.Fc.backup

postgres@localhost:~> psql
psql.bin (9.3.6 (VMware Postgres 9.3.6.0-2686691 release))
Type "help" for help.

postgres=# \c vcdb vc

You are now connected to database "vcdb" as user "vc".

vcdb=> select * from vpx_version;
 ver_id |       version_value
--------+----------------------------
    600 | VirtualCenter Database 6.0

(1 row)

Postgresql: What does those numbers in the tablespace means

Trying to find out what those numbers represent. I know there are tablespace but pg_tablespace does not yield much information.

localhost:/storage/seat/vpostgres/eventtblsp/PG_9.3_201306121/16384 # ll |sort -rn|head -5
total 12704
-rw------- 1 vpostgres users 6193152 Jun  3 16:13 35489
-rw------- 1 vpostgres users 1040384 Jun  3 16:13 41471
-rw------- 1 vpostgres users  983040 Jun  3 16:13 41468
-rw------- 1 vpostgres users  851968 Jun  3 16:13 35499

VCDB=# SELECT pg_catalog.pg_relation_filenode(c.oid) as "Object ID", relname as "Object Name",

VCDB-# relkind='r', o.rolname as "Owner"

VCDB-# FROM pg_catalog.pg_class c

VCDB-#          JOIN pg_catalog.pg_authid o ON o.oid=c.relowner

VCDB-#          JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

VCDB-#          JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database()  WHERE  relfilenode=35489;

 Object ID |  Object Name  | ?column? | Owner

-----------+---------------+----------+-------

     35489 | vpx_event_arg | t        | vc

(1 row)

Not sure why postgresql is such a hassle that most of the critical information represented by numbers and not easily tracked down. 

Postgresql: REINDEX DATABASE requires double quotes within psql.

Learned that, the psql reindex requires double quotes.

I would usually performs the reindex within psql.


VCDB=#
VCDB=# REINDEX DATABASE VCDB;
ERROR:  can only reindex the currently open database
VCDB=# REINDEX DATABASE "VCDB";

NOTICE:  table "pg_catalog.pg_class" was reindexed
NOTICE:  table "pg_catalog.pg_statistic" was reindexed
NOTICE:  table "pg_catalog.pg_type" was reindexed
NOTICE:  table "vc.cis_kv_providers" was reindexed
NOTICE:  table "pg_catalog.pg_authid" was reindexed
NOTICE:  table "pg_catalog.pg_attribute" was reindexed
NOTICE:  table "pg_catalog.pg_proc" was reindexed
NOTICE:  table "vc.cis_kv_keyvalue" was reindexed
NOTICE:  table "pg_catalog.pg_user_mapping" was reindexed
NOTICE:  table "pg_catalog.pg_constraint" was reindexed
NOTICE:  table "pg_catalog.pg_inherits" was reindexed
NOTICE:  table "pg_catalog.pg_index" was reindexed
NOTICE:  table "pg_catalog.pg_operator" was reindexed
NOTICE:  table "pg_catalog.pg_opfamily" was reindexed

Today, for the fun of it, i performed Reindex with command line and it errors out. I do not see a logical reason to have the double quotes.

localhost:/opt/vmware/vpostgres/current/bin # ./reindexdb -U postgres -d VCDB
NOTICE:  table "pg_catalog.pg_class" was reindexed
NOTICE:  table "pg_catalog.pg_statistic" was reindexed
NOTICE:  table "pg_catalog.pg_type" was reindexed
NOTICE:  table "vc.cis_kv_providers" was reindexed
NOTICE:  table "pg_catalog.pg_authid" was reindexed
NOTICE:  table "pg_catalog.pg_attribute" was reindexed
NOTICE:  table "pg_catalog.pg_proc" was reindexed
NOTICE:  table "vc.cis_kv_keyvalue" was reindexed
NOTICE:  table "pg_catalog.pg_user_mapping" was reindexed
NOTICE:  table "pg_catalog.pg_constraint" was reindexed

Postgresql: Porting database into postgres, rename and recreating it.

In this exercise, I accidentally ported my database into postgres default database. I attempted to rename it to something else, then I cannot logon with postgres because the postgres database no longer there. Along the way, I learned something from trial and error. 

I ported my database to postgres where I have forgotten to change the user.

postgres=# create database corkdb;
CREATE DATABASE
postgres=# create user corkuser with password 'password';
CREATE ROLE
postgres=# grant all privileges on database corkdb to corkuser;
GRANT
postgres=# \i outputfile.sql


Showing all the objects been imported to my postgres database!
corkdb=# select datname, pg_database_size(datname)/1024/1024 as size_in_Megabytes  from pg_database order by 2 desc;
  datname  | size_in_megabytes
-----------+-------------------
postgres  |              1425
vcdb      |                41
aurora    |                23
template0 |                 6
template1 |                 6
corkdb    |                 6
(6 rows)

Renaming it to something else.
postgres=# alter database postgres rename to corkdb;
ERROR:  current database cannot be renamed
postgres=# \l
                                        List of databases
   Name    |    Owner     | Encoding |  Collation  |    Ctype    |       Access privileges
-----------+--------------+----------+-------------+-------------+-------------------------------
aurora    | aurora_admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | aurora_admin=CTc/aurora_admin
                                                                 : =Tc/aurora_admin
                                                                 : aurora_app=CTc/aurora_admin
postgres  | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                                 : postgres=CTc/postgres
template1 | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres
                                                                 : aurora=CTc/postgres
                                                                 : =c/postgres
vcdb      | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
                                                                 : postgres=CTc/postgres
                                                                 : vc=CTc/postgres
(5 rows)

Session is locking up my postgres database

postgres=# \c vcdb
psql (8.4.20, server 9.2.9)
WARNING: psql version 8.4, server version 9.2.
         Some psql features might not work.
You are now connected to database "vcdb".
vcdb=# alter database postgres rename to corkdb;
ERROR:  database "postgres" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Tracking down the offending session

vcdb=# select * from pg_stat_activity where datname='postgres';
datid | datname  | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         ba
ckend_start         | xact_start |          query_start          |         state_change          | waiting | state |
                                query
-------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-----------
--------------------+------------+-------------------------------+-------------------------------+---------+-------+--------
------------------------------------------------------------------------------
12870 | postgres | 9103 |       10 | postgres |                  |             |                 |          -1 | 2015-05-22
11:59:03.168286-04 |            | 2015-05-22 12:04:34.236271-04 | 2015-05-22 12:04:34.237251-04 | f       | idle  | ALTER D
EFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA vpx GRANT ALL ON TABLES  TO vc;
(1 row)

<this version does not have pg_terminate function>
kill -15 9103

Successfully renaming the postgres database. Now, I need to re-create the postgres database shell.

vcdb=# alter database postgres rename to corkdb;
ALTER DATABASE


vcdb=# \l
                                        List of databases
   Name    |    Owner     | Encoding |  Collation  |    Ctype    |       Access privileges
-----------+--------------+----------+-------------+-------------+-------------------------------
aurora    | aurora_admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | aurora_admin=CTc/aurora_admin
                                                                 : =Tc/aurora_admin
                                                                 : aurora_app=CTc/aurora_admin
corkdb    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                                 : postgres=CTc/postgres
template1 | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres
                                                                 : aurora=CTc/postgres
                                                                 : =c/postgres
vcdb      | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
                                                                 : postgres=CTc/postgres
                                                                 : vc=CTc/postgres
(5 rows)

Creating the postgres database within psql will not create the database.

vcdb-# create database postgres
vcdb-# \l
                                        List of databases
   Name    |    Owner     | Encoding |  Collation  |    Ctype    |       Access privileges
-----------+--------------+----------+-------------+-------------+-------------------------------
aurora    | aurora_admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | aurora_admin=CTc/aurora_admin
                                                                 : =Tc/aurora_admin
                                                                 : aurora_app=CTc/aurora_admin
corkdb    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                                 : postgres=CTc/postgres
template1 | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres
                                                                 : aurora=CTc/postgres
                                                                 : =c/postgres
vcdb      | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
                                                                 : postgres=CTc/postgres
                                                                 : vc=CTc/postgres
(5 rows)

Has to create the postgres database with "createdb" command instead within the psql

-bash-4.1$ createdb postgres
-bash-4.1$ psql
psql (8.4.20, server 9.2.9)
WARNING: psql version 8.4, server version 9.2.
         Some psql features might not work.
Type "help" for help.

postgres=# \ll
Invalid command \ll. Try \? for help.
postgres=# \l
                                        List of databases
   Name    |    Owner     | Encoding |  Collation  |    Ctype    |       Access privileges
-----------+--------------+----------+-------------+-------------+-------------------------------
aurora    | aurora_admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | aurora_admin=CTc/aurora_admin
                                                                 : =Tc/aurora_admin
                                                                 : aurora_app=CTc/aurora_admin
corkdb    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres  | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                                 : postgres=CTc/postgres
template1 | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres
                                                                 : aurora=CTc/postgres
                                                                 : =c/postgres
vcdb      | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
                                                                 : postgres=CTc/postgres
                                                                 : vc=CTc/postgres
(6 rows)

postgres=# select datname, pg_database_size(datname)/1024/1024 as size_in_Megabytes  from pg_database order by 2 desc;
  datname  | size_in_megabytes
-----------+-------------------
corkdb    |              1425
vcdb      |                41
aurora    |                23
template0 |                 6
template1 |                 6
postgres  |                 6
(6 rows)

postgres=#

Postgres Corruption: ERROR: could not open file "base/.../.."

vCenter crashed and down with errors like the following. At this time, no one has deleted anything manually from the base directory. It just happened out of the blue. This is an incident happened of vCenter Appliance with Postgresql and not uncommon. If indexes were the only corruption, they can be identified and recreated and perhaps reindex will reconstruct the corrupted indexes (I have not personally tried this, just a logical thinking). My guess is, this works similar if index pg_toast missing. If tables involved in the corruptions and disappeared, it is not worth the time and would likely have data integrity issues. Work on a plan to restore up to the point where corruptions no longer showing. There are chances that the backup may inherit the same corruption. This corruption might be detectable with vacuum full.

2015-05-14T10:40:00.011-04:00 warning vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] Execution elapsed time: 4 ms

2015-05-14T10:40:00.011-04:00 warning vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] Statement diagnostic data from driver is 58P01:0:7:ERROR: could not open file "base/16384/26465": No such file or directory;

--> Error while executing the query

2015-05-14T10:40:00.011-04:00 error vpxd[08940] [Originator@6876 sub=Default] [Vdb::IsRecoverableErrorCode] Unable to recover from 58P01:7

2015-05-14T10:40:00.011-04:00 error vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] SQLError was thrown: "ODBC error: (58P01) - ERROR: could not open file "base/16384/26465": No such file or directory;

--> Error while executing the query" is returned when executing SQL statement "select rule_topn1_proc()"

2015-05-14T10:40:00.012-04:00 warning vpxd[08940] [Originator@6876 sub=OsLayer_win32] [VpxUnhandledException] Win32 Exception (3765269347) detected at 000007fefd90aaad

To confirm with missing objects, use can locate it with 'ls' command or the following query.

SELECT pg_catalog.pg_relation_filenode(c.oid) as "Object ID", relname as "Object Name",

case WHEN relkind='r' THEN 'Table' when relkind='m'

THEN 'Materialized View' when relkind='i'

THEN 'Index' when relkind='S'

THEN 'Sequence' when relkind='t'

THEN 'Toast' when relkind='v'

THEN 'View' when relkind='c'

THEN 'Composite' when relkind='f'

THEN 'Foreign_Table'

ELSE 'other' end as "Object Type", o.rolname as "Owner"

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_authid o ON o.oid=c.relowner

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),

pg_catalog.pg_tablespace t

WHERE pg_catalog.pg_relation_filenode(c.oid) = 26465

postgresql corruption: Invalid Page

Upon restarting the postgresql database, it failed with the following error.

invalid page header in block xxxxx of relation pg_tblspc/16385/pg_9.0_201106101/16386/17694

Hint: This probably means that some data is corrupted and you will have to use the last backup for recovery

Turn on the zero_damaged_pages flag in postgresql.conf or within the psql prompt.

Add the following or change it from OFF to ON within postgresql.conf

zero_damaged_pages = on

show zero_damaged_pages;

(this probably state OFF)

SET zero_damaged_pages = on;

vacuum full <TABLE>;

reindex table <TABLE>;

This typically triggered after disk filled up where the postgresql WAL and database mounted together.. I am really not sure what caused this.  Perhaps, unplanned power outage.  It basically just mean corruption happened within the table and the flag simply telling the db engine to skip it. This seems happened quite often. If it can be restored with good backup, that would be more ideal as the workaround still consist of corruption data within. It has been reported that by simply turning the zero_damaged_pages to ON, it simply work again even without the VACUUM FULL and REINDEX. I have also encountered a situation where executing reindex prior to vacuum full, it did not work but reversing the order as the example shown above worked.

Postgresql: porting database

Porting vCenter database into existing Postgres database.

psql>

create database vcdb if not exist;

create user vc with password 'vmware';

grant all PRIVILEGES ON DATABASE vcdb to vc;

psql -U vc -d vcdb -f VCDBback.backup

Example

postgres=# create database vc;
CREATE DATABASE
postgres=# create user vc with password 'mypass!2ft';
CREATE ROLE
postgres=# grant all PRIVILEGES ON DATABASE vc to vc;
GRANT

vc=# grant postgres to vc; 
GRANT ROLE

postgres=# \c vc vc                

You are now connected to database "vc" as user "vc".
vc=> \i VCDBback.backup

Postgresql: VMware vPostgres Cheatsheet

Some of the default vPostgres binaries and data paths for VMware products. Feel free to source the environment file. These are just for my own handy references.

If you have default port other than 5432, you may need that in the startup as well.

vCenter
Home	/opt/vmware/vpostgres/9.3/bin/
Data	/storage/db/vpostgres
Start	/opt/vmware/vpostgres/9.3/bin/postgres -D /storage/db/vpostgres

vMware vCloud Automation Center (VCAC)
Home	/opt/vmware/vpostgres/9.2/bin/
Data	/var/vmware/vpostgres/current/pgdata
Start	/opt/vmware/vpostgres/9.2/bin/pg_ctl -D /var/vmware/vpostgres/current/pgdata start

Hyperic
Home	/opt/vmware/vpostgres/9.1/bin
Data	/opt/vmware/vpostgres/9.1/data
Start	/opt/vmware/vpostgres/current/bin/pg_ctl -D ../data start
Stop	/opt/vmware/vpostgres/current/bin/pg_ctl -D ../data stop

vCloud Director
Home	/opt/vmware/vpostgres/9.0/bin
Data	Opt/aurora/data/db
Start	(as root) service aurora_mon start

NSX
Home	/opt/vmware/vpostgres/9.1/bin/
Data	/common/db/vshield
Start	sudo -u xxxxx /opt/vmware/vpostgres/9.1/bin/pg_ctl -D /common/db/vshield start