postgresql: pg_relation_size glitches?

How did pg_relation_size locate something does not exist in the db?

VCDB=# select current_database(), version();

 current_database |                                                                     version

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

-----------------------------------

 VCDB             | PostgreSQL 9.4.9 (VMware Postgres 9.4.9.1-4536683 release) on x86_64-unknown-linux-gnu, compiled by x86_64-vmk

-linux-gnu-gcc (GCC) 4.8.4, 64-bit

(1 row)

VCDB=# select pg_relation_size(table_name) as Size  FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;

ERROR:  relation "vci_version" does not exist

VCDB=# \d vci_version;

Did not find any relation named "vci_version".

VCDB=#

VCDB=#  select table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog');

           table_name

--------------------------------

 cis_kv_providers

 cis_kv_keyvalue

 vpx_alarm_expression

 vpx_alarm_expr_comp

 vpx_alarm

...

SELECT tablename,  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))

FROM pg_tables WHERE schemaname = 'vc';

           tablename            | pg_size_pretty

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

 cis_kv_providers               | 8192 bytes

 cis_kv_keyvalue                | 8192 bytes

 vpx_alarm_expression           | 112 kB

 vpx_alarm_expr_comp            | 48 kB

 vpx_alarm                      | 104 kB

 vpx_alarm_runtime              | 8192 bytes

postgresql: Confusing piece of search_path in vcdb.

For some reason, every time I need to look at the tables vc owned, even login in with "vc", I have to perform the following to look for objects in the db. This isn't causing much problem but accessing with Python pyscopg2, it will not find the tables.

set search_path to public, vpx;

vcdb=> select usename, useconfig from pg_user

vcdb-> ;

 usename  |      useconfig

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

 postgres |

 vc       | {search_path=public}

(2 rows)

No matter how I changed the search_path in the postgresql.conf. A fresh login just wouldn't let me find my own tables. Finally, I decided to just let database search_path.

vcdb=> alter database vcdb set search_path to public, vpx, vc, postgres;

ALTER DATABASE

vcdb=> \d

No relations found.

vcdb=> \c vcdb

psql (9.4.9, server 9.2.9)

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

vcdb=> \d

                     List of relations

 Schema |              Name              |   Type   | Owner

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

 vpx    | vpx_access                     | table    | vc

 vpx    | vpx_access_seq                 | sequence | 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

 vpx    | vpx_alarm_runtime              | table    | vc

 vpx    | vpx_alarm_seq                  | sequence | vc

 vpx    | vpx_alarm_state                | table    | vc

 vpx    | vpx_ansfile_seq                | sequence | vc

 vpx    | vpx_array_seq                  | sequence | vc

 vpx    | vpx_binary_data                | table    | vc

 vpx    | vpx_binary_data_seq            | sequence | vc

 vpx    | vpx_bulletin_operation         | table    | vc

 vpx    | vpx_change_tag                 | table    | vc

 vpx    | vpx_change_tag_seq             | sequence | vc

 vpx    | vpx_compliance_status          | table    | vc

 vpx    | vpx_compliance_status_seq      | sequence | vc

 vpx    | vpx_compute_res_failover_host  | table    | vc

 vpx    | vpx_compute_res_user_hb_ds     | table    | vc

 vpx    | vpx_compute_resource           | table    | vc

 vpx    | vpx_compute_resource_das_vm    | table    | vc

 vpx    | vpx_compute_resource_dpm_host  | table    | vc

vcdb=>

postgres=# SELECT r.rolname, d.datname, rs.setconfig

postgres-# FROM   pg_db_role_setting rs

postgres-# LEFT   JOIN pg_roles      r ON r.oid = rs.setrole

postgres-# LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase;

 rolname | datname |                 setconfig

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

 vc      |         | {"search_path=public, vpx"}

         | vcdb    | {"search_path=public, vpx, vc, postgres"}

(2 rows)

Voila, Everybody can access now!

postgresql: one of the few ways to start postgresql db under different port.

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

OR

--Needs double code between the port. Otherwise, it will complain. Kept forgetting this after several weeks. The silly thing is, it should have complained about the -p instead of the port number.

-bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl "-p 5433" -D /var/lib/pgsql/9.3/data start

sh:  5433: command not found

server starting

/usr/pgsql-9.3/bin/pg_ctl -o "-p 5433" -D /var/lib/pgsql/9.3/data start

-bash-4.1$ ps -ef|egrep '5432|5433'|grep -v grep

postgres  2040     1  0 Apr27 ?        00:04:44 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data

postgres 14362     1  0 16:48 pts/3    00:00:00 /usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data -p 5433

postgresql: Backup postgresql database

Postgresql has 2 ways of back up.

The first is pg_dump

A simpler form of pg_dump which typically using superuser or the database owner.

pg_dump vcdb > vbdb_db.bak

Backing database for VMware vPostgresql database.

localhost:~ # /opt/vmware/vpostgres/9.3/bin/pg_dump -U postgres -d VCDB -Fc -b -v -f vcdb.backup.compressed

A second method of back will be backup in form of ASCII. 

/opt/vmware/vpostgres/9.3/bin/psql -U postgres -d VCDB > vcdb.bk

postgresql: drop function with return value without knowing it.

Unlike Oracle, Postgresql do not distinct between procedure but considering procedure as function with out return value. Technically, they are the same just procedure without return value  but would be nice to differentiate them. The inconvenient comes when Procedure and Function are considered as the same object type in Postgresql which is function. Dropping a function without parameter would be the way way but what about dropping function with parameter? It is not as easy as Oracle in this matter. Came up with this script and I was able to drop it.

-- Check if the function in the database

vcdb=# \df remove_cafe_resource;

                                      List of functions

 Schema |         Name         | Result data type |       Argument data types       |  Type

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

 public | remove_cafe_resource | void             | resource_name character varying | normal

(1 row)

-- Attempt to drop the function without parameter it should fail but telling me that it does not exist instead.

vcdb=# drop function remove_cafe_resource();

ERROR:  function remove_cafe_resource() does not exist

-- Script to drop it. Copy and paste it to drop the function.

select 'drop function ' || oid::regproc  || '(' || pg_get_function_identity_arguments(oid) || ');' FROM   pg_proc WHERE  proname = 'remove_cafe_resource' AND pg_function_is_visible(oid);

                               ?column?

----------------------------------------------------------------------

 drop function remove_cafe_resource(resource_name character varying);

(1 row)

-- Dropped

drop function remove_cafe_resource(resource_name character varying);

DROP FUNCTION

vcdb=#  \df remove_cafe_resource;

                       List of functions

 Schema | Name | Result data type | Argument data types | Type

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

(0 rows)

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;