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