Basic Error Of PostgreSQL

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:
---------------------
you must either be a superuser or have the CREATEROLE privilege.

The easiest solution to this problem is to not drop the user at all, but just disallow the user from connecting:

pguser=# alter user bob nologin;

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.