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)