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)