Category Archives: postgresql

Issue connecting to database

Issue connecting to database:

[root@ravikumar]# psql -U postgres -d EMSDB
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?

Solution:

1. Try to stop the database

bash-4.2$ ./pg_ctl -D /data/ems/db/ -o -p5342 stop -m fast
pg_ctl: directory “/data/ems/db/” is not a database cluster directory

2. If it gives above error,
i) remove postgres pid or lock files under /tmp
ii) check pg_hba.conf file has correct values

3. Excute the command under postgres_home/bin ./pg_ctl -D /data/aniems/db/ -o -p5342 start

4. If the above step does not work, restart the system

Converting long value in string to timestamp

\d student
Column        |         Type                        | Modifiers
—————-+———————–+———–
id                      | numeric(20,0)             |
name               | character varying(50) |
joiningtime    | character varying(30) |

select id,name,joiningtime,to_timestamp(CAST(coalesce(joiningtime, ‘0’) AS numeric)/1000) from student

id        | name      |  joiningtime     |         to_timestamp
——–+———-+—————+——————————-
168364 | Ravi        | 1484833632817 | 2017-01-19 19:17:12.817+05:30
168351  | Kumar   | 1484833632296 | 2017-01-19 19:17:12.296+05:30

 

Auto vacuum and analyse larger tables

Postgresql database operations becomes slower if table gets larger. In order to improve the performance, we can configure to auto-vacuum the table for every x inserts/updates/deletes.

Using below settings, a table would be auto vacuumed and analysed every 5,000 inserts, updates, or deletes.

ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0.0);

ALTER TABLE mytable SET (autovacuum_analyze_scale_factor = 0.0);

ALTER TABLE mytable SET (autovacuum_vacuum_threshold = 10000);

 

 

Useful SQL queries

#To get all the rows in table student which have the address column as empty

select * from student where coalesce( trim(address),”)!=”;

#Get last 25 records whose time value 30 days greater than current time

SELECT to_timestamp(time/1000) from mytable WHERE to_timestamp(time) > NOW() – INTERVAL ’30 days’ order by desc time limit 25;

#Enable and disable query execution time

MYDB=# \timing
Timing is on.

MYDB=# \timing
Timing is off.
#Enable expanded display

This is an alternative output format. The default output would look clumsy if the query output has more number columns

MYDB=# \x ;
Expanded display is on.
MYDB=# select * from user;
-[ RECORD 1 ]—–+————————————————————————————————–
name            | Ravi

id                | 57622302

#Get count of all tables

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

 

 

Backup postgres database and tables

Backup

#login as root

#For single table

pg_dump -U postgres -d TESTDB -t users > /tmp/users.sql

#For multiple tables

pg_dump -U postgres -d TESTDB -t users -t customer -t account > /tmp/testdb.sql

#For complete database
pg_dump -U postgres TESTDB > /tmp/TESTDB.sql

Restore

#login as postgres user

psql -U postgres -d TESTDB < /tmp/TESTDB.sql