Tuesday, April 26, 2016

Postgresql: pg_indent.conf privilege issue.

Issue happening in porting postgresql database to newly setup database. By default, the database will be ported but will not have direct access to the database with it's assigned user, in this case 'vc'. Upon doing  \dt or select on the the table, it would say relation not found. However, performing a global search on newly ported database, tables will show to exist in designated schema.


SELECT table_schema,table_name
FROM information_schema.tables
where table_schema not in ('pg_catalog','information_schema')
ORDER BY table_schema,table_name;



-bash-4.1$ psql -U vc -d vcdb
psql: FATAL:  Peer authentication failed for user "vc"


edit pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     trust
-bash-4.1$ /usr/pgsql-9.2/bin/pg_ctl  -p 5432 -D /var/lib/pgsql/9.2/data reload
server signaled


########################
Another scenario
########################


vcdb=# SELECT * FROM VC.VERSION;
ERROR:  relation "vc.version" does not exist
LINE 1: SELECT * FROM VC.VERSION;

vcdb=# \c vcdb vc
FATAL:  Peer authentication failed for user "vc"
Previous connection kept


vcdb=# select count(*) from vc.vpx_version;
 count
-------
     1
(1 row)


<< change pg_ident.conf then reload >>

vcdb=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)


vcdb=# \c vcdb vc
psql (9.3.5, server 9.2.9)
You are now connected to database "vcdb" as user "vc".
vcdb=>

Tuesday, April 12, 2016

Postgresql: Changing timezone

Application getting incorrect timezone and suspecting it came from the Postgresql database. Default/Global timezone can be easily changed from postgresql.conf without the need to bounce the database.


1- Check current timezone.
vcdb5=> show timezone;
  TimeZone 
------------
 US/Pacific
(1 row)


2- Check postgresql.conf. Search for "timezone" keyword. 
[postgres@localhost data]$ grep "timezone" postgresql.conf
log_timezone = 'US/Pacific'
timezone = 'US/Pacific'


3- Change timezone to ‘Europe/Vienna’ in the postgresql.conf
timezone ='Europe/Vienna'


4- Reload the conf file from psql 
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)


5- Show the timezone and it is flipped.
postgres=# show timezone;
   TimeZone   
---------------
 Europe/Vienna
(1 row)

Saturday, January 16, 2016

postgresql corruption: transaction wrapped around



postmaster down with the following errors repetitively at the lower section of the log.

Mild wraparound issue

ERROR:  database is not accepting commands to avoid wraparound data loss in database "VCDB"
HINT:  Stop the postmaster and use a standalone backend to vacuum that database.
You might also need to commit or roll back old prepared transactions.


VACUUM FULL ANALYZE <entire database>


Changes are, this issue may not be able to cure. 

Severe wraparound issue

In an extreme case, the wraparound already happened. From my experience, VACUUM FULL or FREEZE or VACUUM on specific offending table (e.g VACUUM VPX_VM) did not work always work.  

ODBC error: (08001) - [unixODBC]FATAL: MultiXactId 1085312546 has not been created yet -- apparent wraparound

Ideally, DBA should restore a good backup. Usually, by the time encountering this type of issue, the database is beyond repaired and no whole lot of luck with it. DBA should really constantly watch out for this type of issue and performing VACUUM FULL at the database as part of maintenance as current postgresql have 4 billion of it before too late. Perhaps, do it once a year ?

DBA can track the current and used up/frozed xid. there should be fairly closed and far from 4 billion or 2^32.

select txid_current(), txid_current_snapshot();

SELECT max(age(datfrozenxid)) FROM pg_database;


Monday, October 26, 2015

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.

Tuesday, August 11, 2015

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.

Monday, July 27, 2015

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.

Thursday, July 9, 2015

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)