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