Monday, 27 January 2014

Handy Queries of PostgreSQL:

Let us start from the basic queries..
to know version:
select version();
to know size of database:
SELECT pg_size_pretty(pg_database_size('mydatabasename')) As fulldbsize;
to know all catalog views:
\dt pg_catalog.*

Looks I am at very basic level. Dont mind, let me go some advance level.. :-)

Query to find Parameters changes for a table:
===============================
SELECT c.relname, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.relname = 'test'

Long Running Queries:
select (now() - query_start)  as query_time_taken,* from pg_stat_activity where xact_start is not null and  ( current_query ilike '%select%' or current_query ilike '
%update%' or current_query ilike '%delete%' or current_query ilike '%truncate%' or current_query ilike '%drop%') and (now() - query_start) > interval '60 min';

Delete Duplicate Values in a table using CTID (pseudo column):
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

Want to GRANT privileges on all tables !!!, dont worry, here is the query to generate script:
SELECT 'grant select,update,usage on '||c.relname||' to username;' FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname='schemaname' AND pg_catalog.pg_get_userbyid(c.relowner)='username';

Ohh Good !! you have granted privileges on tables, now you want to check ??.. here is a query:
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' END as "Type",
  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
    FROM pg_catalog.pg_attribute a
    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  ), E'\n') AS "Column access privileges"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r') AND pg_catalog.pg_get_userbyid(c.relowner)='username' AND n.nspname='schemaname';

Query to find all functions with arguments:
SELECT n.nspname || '.' || p.proname || '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')' as FunctionName,usename as OWNER FROM pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace, pg_user u WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND p.proowner=u.usesysid AND n.nspname not in ('pg_catalog','sys');

-- OR

select proname||'('||pg_get_function_arguments(pg_proc.oid)||')' as function_arguments,usename,nspname from pg_proc,pg_user,pg_namespace where  proowner=pg_user.usesysid and pronamespace=pg_namespace.oid and usename<>nspname and nspname !~ '^pg_catalog|^information_schema|^sys';

Hmm.. You have granted some privileges to a user, but forgot to notedown. Here is a query to find the privileges of a user on objects:
SELECT n.nspname as "Schema",
    c.relname as "Name",
    CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",
    pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
    pg_catalog.array_to_string(ARRAY(
      SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
      FROM pg_catalog.pg_attribute a
      WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
    ), E'\n') AS "Column access privileges"
  FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind IN ('r', 'v', 'S', 'f')
    AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid) and pg_catalog.pg_get_userbyid(c.relowner)='enterprisedb'
  ORDER BY 1, 2;

Ok, looks you have migrated your tables from one database(server) to another database(server), hmm.. good. But now you should check if all tables and their data are migrated sucessfully or not. Ah, something difficult but you can try below query:
To get list of all tables and their row count:
SELECT
pgClass.relname AS tableName,
pgClass.reltuples AS rowCount
FROM
pg_class pgClass
LEFT JOIN
pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE
pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
pgClass.relkind='r';

Want to check tables in each user defined schema, here you go:
SELECT n.nspname as "Schema",
  count(c.relname) as "Name"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
 group by n.nspname;

You want a alternative of "sysdate" function in PostgteSQL like Oracle and Postgres Plus Advance Server, good thought .. Keep it up !!..
edb=# \df sysdate
                                 List of functions

   Schema   |  Name   |      Result data type       | Argument data types |  Type 
------------+---------+-----------------------------+---------------------+--------
pg_catalog | sysdate | timestamp without time zone |                     | normal
(1 row)
--However in PG, we will have to create "sysdate" function without paranthesis where PG does not allow us to create the function without parathesis.

postgres=# create function pg_catalog.sysdate returns date language sql as 'SELECT PG_CATALOG.current_date';

ERROR:  syntax error at or near "returns"

LINE 1: create function pg_catalog.sysdate returns date language sql...

--If we create with paranthesis, then we should call it with parathensis.

postgres=# create or replace function pg_catalog.sysdate() returns date language sql as 'SELECT current_date';
CREATE FUNCTION
postgres=# select sysdate;
ERROR:  column "sysdate" does not exist
LINE 1: select sysdate;
               ^
postgres=# select sysdate();
  sysdate 
------------
 2013-08-01

Use this query to read total number of transactions executed in all databases:
SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;

If you want the same counter for just one database, use:
SELECT xact_commit+xact_rollback FROM pg_stat_database WHERE datname = 'mydb';

By mistake you have created all tables in UPPER CASE !!!.. Here is a query to generate a script to change or rename all table names to LOWER CASE:
SELECT 'alter table "'||c.relname||'" rename to '||lower(c.relname)||';'
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind ='r'
      AND n.nspname='public'
ORDER BY 1;

Oops even column names are also in UPPER CASE, No worries, here is a query to generate a script to change or rename all columns of a table:
For One table:
SELECT
        'alter table "'||c.relname||'" rename "'||a.attname||'" to '||lower(a.attname)||';'
FROM
        pg_class c
        JOIN pg_attribute a ON a.attrelid = c.oid
        JOIN pg_type t ON a.atttypid = t.oid
        LEFT JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
                AND r.conname = a.attname
WHERE
        c.relnamespace = (select oid from pg_namespace where nspname='schemaname')
        AND a.attnum > 0 AND c.relkind ='r'
      
ORDER BY a.attnum

For all tables in a schema:
SELECT 
        'altet table "'||c.relname||'" rename "'||a.attname||'" to '||lower(a.attname)||';'
FROM
        pg_class c
        JOIN pg_attribute a ON a.attrelid = c.oid
        JOIN pg_type t ON a.atttypid = t.oid
        LEFT JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
                AND r.conname = a.attname
WHERE
        c.relnamespace = (select oid from pg_namespace where nspname='schemaname')
        AND a.attnum > 0
      
ORDER BY a.attnum


Sunday, 26 January 2014

Copy all objects of one schema to other schema with in same database


There are two ways to copy all objects of one schema to other schema with in the same database.

Procedure 1:
------------
1. Take the dump of Source schema using below command.
$./pg_dump -U <username> -p <port> -n <schema name> <database name> >> <dump filename>
Ex:-
-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_dump -U postgres -p 5435 -n test postgres >> /tmp/test_dmp.sql
2. Open the dumpfile which was created in step 1, and remove the "CREATE SCHEMA" and "ALTER SCHEMA" commands. Below are for example:
CREATE SCHEMA test;

ALTER SCHEMA test OWNER TO postgres;
3. Modify the Source schema name with Target schema name in the dump file.
4. Restore in the database by using below command.

$./psql -U <username> -p <port> -d <database> -f <dump filename>

Ex:- 
$./psql -U postgres -p 5435 -d postgres -f dump.sql
Or connect to psql prompt and execute the dump file from target database.
postgres=#\i <dump file location>
Ex:- #\i dump.sql
Procedure 2:
 ------------ --
-- Rename the target schema to different name and rename the source schema to target schema name. Take the dump of source schema which is renamed to target schema(You can use step 1 in procedure 1 to take the dump). And rename the source schema and target schema to their previous names. And then restore the schema(you can use step 4 in procedure 1 to restore the dump)

 For example your source schema name is test1 and target schema name is test2.
 1. Rename the test2 schema to test3 and rename the test1 schema to test2.
postgres=#ALTER SCHEMA <schemaname> RENAME TO <schemaname>;
2. Take the dump of test2.
$./pg_dump -U <username> -p <port> -n <schema name> <database name> >> <dump filename>
3. Rename test2 to test1 and test3 to test2.
postgres=#ALTER SCHEMA <schemaname> RENAME TO <schemaname>;
4. Restore the dump of test2 which is taken in step 2.
$./psql -U <username> -p <port> -d <database> -f <dump filename>
Note: You can ignore the errors of schema already exists during the restore process as target schema is already exists.

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 ;-)