postgresql: Owner to access schema. set public.

Unable to access schema that vc do not owned but have to specify the schema every time. This can be set.

postgres=# \c vcdb

psql (9.3.5, server 9.2.9)

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

vcdb=# \dt

No relations found.

vcdb=# \dt vpx.vpx_version

          List of relations

 Schema |    Name     | Type  | Owner

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

 vpx    | vpx_version | table | vc

(1 row)

vcdb=# SET search_path TO vpx,public;

SET

vcdb=# \dt vpx_version;

          List of relations

 Schema |    Name     | Type  | Owner

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

 vpx    | vpx_version | table | vc

(1 row)

vcdb=# \dt

                    List of relations

 Schema |              Name              | Type  | Owner

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

 vpx    | vpx_access                     | table | vc

 vpx    | vpx_alarm                      | table | vc

 vpx    | vpx_alarm_action               | table | vc

 vpx    | vpx_alarm_disabled_actions     | table | vc

 vpx    | vpx_alarm_expr_comp            | table | vc

 vpx    | vpx_alarm_expression           | table | vc

 vpx    | vpx_alarm_repeat_action        | table | vc

Postgresql: database running out of space.

What we are going to do here are the following:

Trying to import a database and getting this.

psql:VCDBackUp:89387594: ERROR:  could not extend file "base/16384/25122": wrote only 4096 of 8192 bytes at block 156

HINT:  Check free disk space.

psql:VCDBackUp:89387601: ERROR:  could not extend file "base/16384/25123": No space left on device

HINT:  Check free disk space.

Basically, df -h showing 100% used

• mount a new drive /media/data
• copy 9.3 data path to /media/data/data
• create symlink to point to the new place.
• chown the /media/data to postgresql:postgresql

Verifying what to move. Basically, the "data" directory under 9.3
-bash-4.1$ ls
9.2  9.3  backup  logfile
-bash-4.1$ cd 9.3
-bash-4.1$ ls
backups  data  pgstartup.log

Move the data to /media/data
-bash-4.1$ mv data /media/data/

Changing the owner of the data directory as /media/data is currently own by root.

root@postgres6ec-001 media]# ls
data
[root@postgres6ec-001 media]# chown postgres:postgres data

Create symlink

-bash-4.1$ pwd
/var/lib/pgsql/9.3
-bash-4.1$ ln -s /media/data/data /var/lib/pgsql/9.3/data
-bash-4.1$ ls -las
total 16
4 drwx------. 3 postgres postgres 4096 Apr 26 15:36 .
4 drwx------. 5 postgres postgres 4096 Mar  2  2015 ..
4 drwx------. 2 postgres postgres 4096 Jul 23  2014 backups
0 lrwxrwxrwx. 1 postgres postgres   16 Apr 26 15:36 data -> /media/data/data
4 -rw-------. 1 postgres postgres 3676 May  5  2015 pgstartup.log
-bash-4.1$

Verify if the intended 9.3 postgresql database is not up and running.

-bash-4.1$ ps -ef|grep postgres
avahi     1852     1  0  2015 ?        00:00:13 avahi-daemon: running [postgres6ec-001.local]
postgres  2127     1  0  2015 ?        00:07:38 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data
postgres  2129  2127  0  2015 ?        00:00:00 postgres: logger process
postgres  2131  2127  0  2015 ?        00:00:15 postgres: checkpointer process
postgres  2132  2127  0  2015 ?        00:05:04 postgres: writer process
postgres  2133  2127  0  2015 ?        00:05:00 postgres: wal writer process
postgres  2134  2127  0  2015 ?        00:06:28 postgres: autovacuum launcher process
postgres  2135  2127  0  2015 ?        00:09:54 postgres: stats collector process



Start up the postgresql 9.3 under port new port 5433 as 5432 is used by 9.2.

bash-4.1nohup /usr/pgsql-9.3/bin/postgres -p 5433 -D /var/lib/pgsql/9.3/data &


Check the new 9.3 database status
-bash-4.1$ ps -ef|grep postgres
avahi     1852     1  0  2015 ?        00:00:13 avahi-daemon: running [postgres6ec-001.local]
postgres  2127     1  0  2015 ?        00:07:38 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data
postgres  2129  2127  0  2015 ?        00:00:00 postgres: logger process
postgres  2131  2127  0  2015 ?        00:00:15 postgres: checkpointer process
postgres  2132  2127  0  2015 ?        00:05:04 postgres: writer process
postgres  2133  2127  0  2015 ?        00:05:00 postgres: wal writer process
postgres  2134  2127  0  2015 ?        00:06:28 postgres: autovacuum launcher process
postgres  2135  2127  0  2015 ?        00:09:54 postgres: stats collector process
postgres 13442 13285  0 15:55 pts/2    00:00:00 /usr/pgsql-9.3/bin/postgres -p 5433 -D /var/lib/pgsql/9.3/data
postgres 13443 13442  0 15:55 ?        00:00:00 postgres: logger process
postgres 13445 13442  0 15:55 ?        00:00:00 postgres: checkpointer process
postgres 13446 13442  0 15:55 ?        00:00:00 postgres: writer process
postgres 13447 13442  0 15:55 ?        00:00:00 postgres: wal writer process
postgres 13448 13442  0 15:55 ?        00:00:00 postgres: autovacuum launcher process
postgres 13449 13442  0 15:55 ?        00:00:00 postgres: stats collector process



Add the new disk (/dev/sdb1) to automount on start up.



-bash-4.1$ cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Tue May 15 13:38:30 2012
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/VolGroup-lv_root /                       ext4    defaults        1 1
UUID=be520c4e-c945-485c-b956-5bba238ef87c /boot                   ext4    defaults        1 2
/dev/mapper/VolGroup-lv_swap swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0

/dev/sdb1       /media/data                     ext4    defaults        0 0

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=>

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)

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;

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.