Friday, 17 January 2014

Basic Errors of PostgreSQL Part1

I thought a lot about my first post of this year, finally came up with this. This includes basic errors of PostgreSQL(which I remember/faced) and how to troubleshoot with those. Mostly this is breaking the things more than fixing, if anyone wants to play with postgres troubleshooting.. ;-)

Error1:
--------
-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres
psql.bin: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5435"?
Cause/Resolution:
--------------------
1. First thing you would need to check is server status(using below commands), if server is not running, start it and try to connect.
-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data/ status
pg_ctl: server is running (PID: 49230)
/opt/PostgreSQL/9.3/bin/postgres "-D" "../data"

-bash-4.1$ ps -ef|grep data
postgres 49230     1  0 13:32 pts/1    00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D ../data
postgres 57430 27596  0 18:23 pts/1    00:00:00 grep data
2. If you found that cluster is running and still not able to connect, then check the port number in postgresql.conf file and try to connect using correct port.
-bash-4.1$ grep -i 'port' /opt/PostgreSQL/9.3/data/postgresql.conf
port = 5435  # (change requires restart)

-bash-4.1$ ls -ltr /tmp/.s.PGSQL.5435*
-rw-------. 1 postgres postgres 51 Jan 12 13:05 /tmp/.s.PGSQL.5435.lock
srwxrwxrwx. 1 postgres postgres  0 Jan 12 13:05 /tmp/.s.PGSQL.5435

-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres
Timing is on.
psql.bin (9.3.2)
Type "help" for help.
postgres=#
Error2:
--------
-bash-4.1$ ./psql -p 5435 -U postgres -h 192.168.225.185 postgres
psql: could not connect to server: Connection refused
Is the server running on host "192.168.225.185" and accepting
TCP/IP connections on port 5435?
Cause/Resolution:
--------------------
1. You would need to look at your "listen_addresses" parameter in postgresql.conf file, check if you are set this to allow the other servers to connect.
-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres postgres
Timing is on.
psql.bin (9.3.2)
Type "help" for help.
postgres=# show listen_addresses ;
 listen_addresses
------------------
localhost
(1 row)

2. If you found that you set it to allow, then you will have to look at your firewall setting. May be port is not opened for that server due to security issues.
Error3:
--------
-bash-4.1$ ./psql -p 5435 -U postgres -h 192.168.225.185 postgres
psql: FATAL:  no pg_hba.conf entry for host "192.168.225.130", user "postgres", database "postgres", SSL off
Cause/Resolution:
--------------------
As error says, there is no entry for that host in pg_hba.conf file(which is loacted at data directory location). You can add an entry for that host like below:
-- Open pg_hba.conf

-bash-4.1$ vi /opt/PostgreSQL/9.3/data/pg_hba.conf

-- Add a line like below:

host    all             all             192.168.225.130/32            trust

Note: trust is a type of authentication. you will get more info here: http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html

-- Reload the cluster.

-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data/ reload
server signaled

-- Now try to connect.

-bash-4.1$ ./psql -p 5435 -U postgres -h 192.168.225.185 postgres
psql (9.3.0.1, server 9.3.2)
Type "help" for help.
postgres=#
Error4:
--------
-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U postgres -d postgres
psql.bin: FATAL:  sorry, too many clients already
Cause/Resolution:
---------------------
This error shows that you are done with max_connections, check the parameter and connections to the cluster from an already connected session:
postgres=# show max_connections ;
 max_connections
-----------------
 2
(1 row)
postgres=# select count(*) from pg_stat_activity;
 count
-------
     2
(1 row)
To get rid of this, you probably need to increase the parameter or disconnect some "idle" sessions. You will get idle sessions by using below query:
postgres=# select pid,query,state from pg_stat_activity where state like 'idle';
  pid  | query | state
-------+-------+-------
 11855 |       | idle
(1 row)
postgres=#
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where state='idle' and pid <> pg_backend_pid();
 pg_terminate_backend
----------------------
 t
(1 row)
postgres=# select pid,query,state from pg_stat_activity where state like 'idle';
 pid | query | state
-----+-------+-------
(0 rows)
postgres=#

Note: changing any of the above parameters needs a restart of cluster.
Error5:
--------
-bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 -U test postgres
psql.bin: FATAL:  remaining connection slots are reserved for non-replication superuser connections
Cause/Resolution:
--------------------
As error says, remanining connections are reserverd for superusers. So you would need to increase max_connections parameter or decrease the superuser_reserved_connections parameter to connect as *normal* user.
Note: changing any of the above parameters needs a restart of cluster.

Error6:
--------
postgres=# select * from test;
ERROR:  relation "test" does not exist
LINE 1: select * from test;
                      ^
Cause/Resolution:
---------------------
1. First question, might be silly, but worth to ask, really this table exist?
2. yes, exists, OK, then check whether the table name given is correct or not. You might have created the table with mixed chars(upper/lower). You can get the exact name by using below query:
postgres=# select quote_literal(relname) from pg_class where upper(relname)='TEST';
 quote_literal
---------------
 'TesT'
(1 row)
postgres=# select * from "TesT";
 t
---
(0 rows)
3. Check if you have the table in different schema so that you can specify the schema name explicitly before the table name OR set the schema name in searth_path parameter:
postgres=# \d '*'."TesT"
      Table "test.TesT"
 Column |  Type   | Modifiers
--------+---------+-----------
 t      | integer |


-- So you have table in "test" schema, then use below query or set search_path like below:

postgres=# select * from "test"."TesT";
 t
---
(0 rows)
postgres=# set search_path to "test";
SET
postgres=# select * from "TesT";
 t
---
(0 rows)
Error7:
--------
testdb=# drop user bob;
ERROR: role "bob" cannot be dropped because some objects depend on it
DETAIL: owner of table bobstable
owner of sequence bobstable_id_seq
Cause/Resolution:
---------------------
For dropping such user, there are two methods: 1. Reassign all the objects owned by the user to some other user and then drop the user.
Above is very useful, if employee, who left the company, has written some Procedure/objects, which is getting used in Application/process.

Command Which can be are following
 REASSIGN OWNED BY old_role to new_role;  
    DROP USER old_role; 
Note:: reassign command need to be executed for all the databases under one PG instance.
2. First Drop all the objects owned by the user and then drop the user.
This is useful if admin don't want to keep the users objects and wants to drop all the objects owned by user:
Command which can be use are following:
DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ];
    DROP user username; 
Note:: DROP OWNED BY NAME need to be executed for all the database.
Error8:
--------
"LOG: out of file descriptors: Too many open files in system; release and retry"
Cause/Resolution:
--------------------
If you see this error Message in Log file then consider reducing Potgres's max_files_per_process setting. Postgres itself will usually not have a serious problem when you've run the kernel out of file descriptors or use ulimit -n command max_files_per_process to something less than whatever per-process file limit the kernel is enforcing.
Error9:
--------
postgres=> copy test from '/tmp/test.txt';
ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
Cause/Resolution:
---------------------
As error says, a normal user can't copy from a file to a table. You can use "\COPY" instead.
postgres=> select current_user;
 current_user
--------------
 test
(1 row)
postgres=> \copy test from '/tmp/test.txt';
postgres=> select * from test;
 t
---
 1
 2
 3
 4
 5
(5 rows)
OR
To let user "test" copy directly from file, the superuser can write a special wrapper function for "test" user, as follows:  
create or replace function copy_for_testuser(tablename text, filepath text)
   returns void
   security definer
   as
   $$
    declare
    begin
         execute 'copy ' || tablename || ' from ''' || filepath || '''';
    end;
   $$ language plpgsql;

postgres=# \c postgres test
You are now connected to database "postgres" as user "test".
postgres=>
postgres=> select copy_for_testuser('test','/tmp/test.txt');
 copy_for_testuser
-------------------

(1 row)
postgres=> select * from test;
 t
---
 1
 2
 3
 4
 5
(5 rows)
Error10:
---------
ERROR:  canceling statement due to statement timeout
Cause/Resolution:
--------------------
Use statement timeout to clean up queries which take too long. Often you know that you don't have any use for queries running more than x times. Maybe your web frontend just refuses to wait for more than 10 seconds for a query to complete and returns some default answer to users if it takes longer, abandoning the query.
In such a case, it is a good idea to set statement_timeout = 15 sec either in postgresql.conf or as a per user or per database setting, so that queries running too long don't consume precious resources and make others' queries fail as well.
The queries terminated by statement timeout show up in log as follows:
 test=# set statement_timeout = '3 s';
   SET
   test=# select wait(10);
   ERROR:  canceling statement due to statement timeout
They used to show up as a more confusing "query canceled due to user request" on the older version of PostgreSQL.

Thank you all, will be posting more ERRORs soon.... there are a lot to post ;-)

5 comments:

  1. which is the "older version of PostgreSQL" you refer to? :)

    ReplyDelete
  2. Thanks you so much.

    ReplyDelete
  3. Awesome,
    Baji can you please provide migration from oracle database to postgresql database document, would be highly appreciated

    ReplyDelete
    Replies
    1. Hi Altaf,

      Thanks for reading the post. Reg your question, there is no idea document for Oracle to PostgreSQL migration as depends on many factors. However, personally, I have used Ora2pg tool for schema migration and Pentaho for data migration.

      Delete