Small heaps of code

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Installation & Setup ======

For Ubuntu 12.04:

<code bash>
sudo apt-get install postgresql-9.1 pgadmin3
</code>

pgAdmin III is a strongly recommended GUI for twiddling with your PostgreSQL. However, here are the first steps with the command line right after installing.

===== Server Configuration =====

==== Enable remote TCP/IP connections ====

Allow TCP/IP connections from all IPs (default = localhost only) by adding this line to ''/etc/postgresql/9.1/main/postgresql.conf'':

<code>
listen_addresses = '*'
</code>

Then allow password authentication for all users from all IPs by adding this line to ''/etc/postgresql/9.1/main/pg_hba.conf'':

<code>
host    all         all         0.0.0.0/0               md5
</code>


To further twiddle with more restrictive client authentication settings have a look at [[http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html|pg_hba.conf]].


===== User Management =====

First steps with a user of your choosing

<code bash>
# log in with user created by default (which has no password set)
sudo -u postgres psql

# create a user with your linux user name and log in
# (in Ubuntu the postgres user that matches your linux user name
# can by default log in without supplying a password) 
sudo -u postgres createuser --superuser $USER
psql -d postgres
# set a password
\password

# create a restricted user with password (long and short version)
sudo -u postgres createuser --no-superuser --no-createdb --no-createrole --pwprompt username
sudo -u postgres createuser -SDRP username
psql -U username -d postgres
</code>

<code bash>
# drop a user
sudo -u postgres dropuser username
</code>

==== Setting a password for user postgres ====
<code sql>
alter user postgres with password 'password';
</code>


==== Create Readonly User ====
**1. Create the user**
<code bash>
sudo -u postgres createuser --no-superuser --no-createdb --no-createrole --pwprompt username
</code>

**2. Grant readonly permissions**

This is the tedious part (in postgres versions pre 9.0). We need to grant the user the permission to
  * connect to the DB
  * use the schema
  * perform a select on everay object in the schema

<code sql>
GRANT CONNECT ON DATABASE mydb TO username;
GRANT USAGE ON SCHEMA public TO username;

-- Generate GRANT SELECT statements for all tables/views in the schema
SELECT 'GRANT SELECT ON public.' || relname || ' TO username;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v');
</code>

=== Slightly different approach ===
Creating a true read-only user is a real pain in postgres. Just revoking privileges from a user still allows him to create database objects which he owns. You need to revoke the CREATE and USAGE privileges from the SCHEMA public and the GROUP public. 

<code sql>

-- Revoke default permissions from public group:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

-- Add back permissions for the users allowed to create stuff
GRANT CREATE ON SCHEMA public TO rw_user;
GRANT USAGE ON SCHEMA public TO rw_user;

-- Add SELECT permissions for read-only user
GRANT USAGE ON SCHEMA public to readonly_user;
GRANT SELECT ON my_table to readonly_user;

</code>

see http://www.ruizs.org/archives/89 for details. 


==== Grant on all tables ====

With a single [[http://www.postgresql.org/docs/current/static/sql-grant.html|GRANT command]] permission for all tables in a schema can be changed:
<code>
GRANT ALL ON ALL TABLES IN SCHEMA public TO user;
</code>

==== Change owner on all tables ====

CLI: Unfortunately this can only be achieved via scripting, but we don't have to reinvent the wheel: https://gist.github.com/jirutka/afa3ce62b1430abf7572

GUI: Or in pgadminIII right-click on the schema, go to properties, and set the owner to your liking.
===== PSQL meta-commands =====

After you logged into PostgreSQL it's good to know some often-used meta-commands (read more in ''man psql''):

<code sql>
-- list all users
\du

-- list all schemas
\dn

-- list tables
\d
\d+  -- (also show comments)
\d tablename -- show details of a table

-- list all tables / tables of a certain schema
\dt *.* -- list all tables in DB
\dt schemaname.* -- list all tables in a certain schema

-- list all views
\dv *.* -- list all views in DB

-- list all databases on the host
\l

-- connect to a database
\c dbname

-- execute a command on the Linux shell (as-is)
\! command

-- quit
\q
</code>

For more meta-commands check out [[http://www.postgresql.org/docs/9.1/static/app-psql.html|the documentation]].


===== List and Disconnect Active Sessions =====

<code sql>
-- list active connections (and get procpids)
SELECT * FROM pg_stat_activity;

-- cancel a process but leave the user's session open
SELECT pg_cancel_backend( <procpid> );

-- terminate a user's query and session
SELECT pg_terminate_backend( <procpid> );
</code>
====== DB Management ======
===== Show DB Sizes =====
<code sql>
SELECT pg_database.datname,  
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size  
  FROM pg_database 
  ORDER BY pg_database_size(pg_database.datname) desc;
</code>

===== Find large databases =====
<code sql>
SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS Size
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20
</code>

see also: https://wiki.postgresql.org/wiki/Disk_Usage

===== Find large tables (in one DB) =====
<code sql>
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;
</code>


===== Show Table size =====

<code sql>
-- size of data
SELECT pg_size_pretty(pg_total_relation_size('<TABLE_NAME>'));

-- size of indices
SELECT c2.relname, c2.relpages * 8 / 1024 AS "MB"
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = '<TABLE_NAME>' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid
ORDER BY c2.relname;
</code>
====== SQL ======
===== Date and Time =====
  * http://www.postgresql.org/docs/9.1/interactive/datatype-datetime.html
  * http://www.postgresql.org/docs/9.1/static/functions-datetime.html

==== Add 15-min interval to time ====
<code sql>
   SELECT DATE(timestamp) + (interval_id) * time '00:15:00' FROM ...
</code>
==== Extract date-parts ====
<code sql>
   select extract(minute from timestamp '2008-08-01 12:14:13');
</code>
see http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

==== Extract TIME from TIMESTAMP ====
<code sql>
SELECT "time"(mytimestamp) FROM table; 
</code>

==== Convert Timestamp to Seconds ====
To convert a timestamp / interval to seconds (in case of timestamp seonds  since 01-01-1970)

<code sql>
   SELECT EXTRACT (EPOCH FROM  timestamp_or_interval );
</code>

==== Creating Timestamps from Text Fields with missing leading Zeros ====

If your data looks like this:

"2011/3/15 ";"6:17:3 "

You can construct timestamps using the "FM" (fill mode) modifier:

<code sql>
   SELECT to_timestamp(utc_date|' '|utc_time,'YYYY/FMMM/FMDD FMHH24:FMMI:FMSS' );
</code>


==== Update parts of date ====
<code sql>
update datetesting set d = to_timestamp( to_char (d,'YYYY-MM-DD 12:00:00'),'YYYY-MM-DD HH24:MI:SS' ) ;
</code>


==== Time Zone Information ====
Boy that was confusing so here it goes...

=== Converting from string to TIMESTAMP ===


<code sql>
select '2013-05-18 12:30:00'::TIMESTAMP WITH TIME ZONE ; 
-- 2013-05-18 12:30:00+02         no timezone info provided. Postgres simply assumes the current TIME ZONE

select'2013-05-18 12:30:00+00'::TIMESTAMP WITH TIME ZONE as with_tz, '2013-05-18 12:30:00+05'::TIMESTAMP WITHOUT TIME ZONE as without_tz;
--        with_tz         |     without_tz      
------------------------+---------------------
-- 2013-05-18 14:30:00+02 | 2013-05-18 12:30:00
</code>

**Notes**
  * When no TIME ZONE info is provided, but Postgres wants TIME ZONE info, then it simply assumes the current TIME ZONE of the session.
  * Timestamps WITH TIME ZONE are displayed in the TIME ZONE of the current session. Unless explicitly requested otherwise (see later).
  * The TIME ZONE part of a string will simply be **ignored** if you are converting to TIMESTAMP WITHOUT TIME ZONE
    * this also applies when you insert to a column of type TIMESTAMP WITHOUT TIME ZONE

<code sql>
SELECT '2013-05-18 12:30:00+06'::TIMESTAMP;
-- 2013-05-18 12:30:00         --> the  +06 has been completely ignored


SELECT '2013-05-18 12:30:00+06'::TIMESTAMP WITH TIME ZONE;
-- 2013-05-18 08:30:00+02      -->   6:30 UTC --> 8:30CET (+02)

</code>


=== Explicitly setting the TIME ZONE ===
<code sql>
set TIME ZONE 'UTC';
select '2013-05-18 12:30:00+00'::TIMESTAMP WITH TIME ZONE;
-- 2013-05-18 12:30:00+00



set TIME ZONE 'CET';
select '2013-05-18 12:30:00+00'::TIMESTAMP WITH TIME ZONE;
-- 2013-05-18 14:30:00+02


</code>



=== Converting from epoch values to TIMESTAMP ===
<code sql>
select to_timestamp(1358749391);
 
--> "2013-01-21 07:23:11+01"
</code>

**Notes:**
  * Postgres converts the epoch value to TIMESTAMP WITH TIME ZONE. Thats why the result is displayed in the local time zone (CEST, winter time).

=== Discard TIME ZONE Information ===
<code sql>
select to_timestamp(1358749391)::timestamp without time zone;
--> "2013-01-21 07:23:11"
</code> 
**Notes**
  * does the same thing as before, but also discards the time zone information and returns a TIMESTAMP WITHOUT TIME ZONE

=== Convert to other TIME ZONE === 
<code sql> 
select
  to_timestamp(1358749391) at time zone 'utc'; 

--> "2013-01-21 06:23:11"
</code>

**Notes**
  * Displays the value at time zone  UTC -->  6:23
  * The result is a TIMESTAMP WITHOUT TIME ZONE. In this case postgres effectively removes the time zone information from the result
    * see http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT 




===== Useful Functions =====
==== Aggregate Concat ====
<code sql>

  CREATE AGGREGATE textcat_all(
    basetype    = text,
    sfunc       = textcat,
    stype       = text,
    initcond    = ''
  );
</code>
**Usage:**

<code sql>
   SELECT company_id, textcat_all(employee | ', ') FROM mytable GROUP BY company_id;
</code>

==== row_number ====

Is e.g. useful if you need an OID where none is present:

<code sql>
SELECT  row_number() OVER (ORDER BY NULL) AS oid FROM table;
</code>

the "order by" clause is required, but in this special case we do not require any particular order, so we use NULL here.

==== Round time to interval ====
<code sql>
CREATE OR REPLACE FUNCTION round_time_to_interval(TIMESTAMP) 
RETURNS TIMESTAMP AS $$ 
  SELECT date_trunc('hour', $1) + INTERVAL '15 min' * FLOOR(date_part('minute', $1) / 15.0) 
$$ LANGUAGE SQL;
</code>


==== Rows to Columns ====
This is an obscure one, but it is very handy if you must transform a number of rows in a number of columns. This can be achieved with the crosstab() function provided by the tablefunc package of postgres-contrib.  So first we need to install postgresql-contrib and enable the function in our db:

<code bash>
sudo apt-get install postgresql-contrib
sudo service postgresql-8.4 restart
cd /usr/share/postgresql/8.4/contrib
psql -U username -d dbname -h localhost -f tablefunc.sql
</code>

Next have a look at the documentation of crosstab here: http://www.postgresql.org/docs/8.4/static/tablefunc.html


Here is an example of how to aggregate a traffic state from rows to columns (for a number of known roads). 

<code sql>
SELECT * FROM
crosstab(
	'SELECT valid_from, road_id, q from ait.its_traffic_state WHERE road_id IN (23005242,23008577) order by 1',
	'SELECT distinct road_id FROM ait.its_traffic_state WHERE road_id IN (23005242,23008577) order by 1'

) 
AS its_traffic_state(time timestamp, road1 integer, road2 integer);
</code>
...for a higher number of columns you may need a script to generate the column_name/datatype row at the end of the query (if anyone knows a better solution plz tell).



===== Updating Sequence Counters =====
When data is inserted into the database with a specified value for the autoincrementing column the sequence is not touched and must be updated manually.
This is the case when e.g. importing dumps using COPY, or a simple INSERT statement with the autoincrementing column set to a value.

While the existing values (e.g. id 1) are reused the sequence counters stay the same (i.e. start with 0): this prevents new data records from being created as the new object would have a duplicate object id (1 == 1).

The fix:
<code sql>
SELECT SETVAL('sequence_name', MAX(auto_incrementing_column)) FROM table_name;
</code>


===== SQL Hints =====

==== Timeseries completion ====
Assume you have incomplete timeseries in your database but want to select a complete series (pad the missing values with a Zero). The following query should take care of the padding and replacing missing values by 0-values:

<code sql>
SELECT intervals.intervals as interval, coalesce(q,0)
FROM timeseries t 
RIGHT OUTER JOIN generate_series(0,24) intervals
ON t.interval = intervals.intervals ; 
</code>

==== Timeseries creation ====
To create a series of timestamps
<code>
select * from
generate_series('2013-11-01'::timestamp, '2013-11-02'::timestamp, '15 minutes')
</code>
==== Convert Array to Rows ====
This might be useful to replace  WHERE x IN ('a','b','c')  statements (which are notoriously slow in postgres) with JOINs.

<code sql>
SELECT i, (ARRAY['a','b','c'])[i] 
FROM generate_series(1,array_upper(ARRAY['a','b','c'],1)) i;


 i | array
---+-------
 1 | a
 2 | b
 3 | c

(3 rows)
</code>                                                                                                     

It is not very elegant though especially as you need to specify the array twice (or know the size of the array, then you can replace the array_upper statement with the size). If someone has a better solution I would be interested :) 

====== Stored Procedures ======
===== PL/Python =====
  * http://www.postgresql.org/docs/8.3/interactive/plpython.html

==== Aggreagte Functions in PL/Python ====
  * Aggregates in Postgres (Basics) : [[http://www.postgresonline.com/periodical.php?i_id=7#item-67]]
  * PL/Python Specific : [[http://www.postgresonline.com/journal/index.php?/archives/102-PLPython-Part-4-PLPython-meets-aggregates.html ]]

===== PL/Java =====
==== Installing ====
Download the most recent version from http://pgfoundry.org/frs/?group_id=1000038 . Most of the instructions in this sections are taken from the README file in the tarball.
  * unzip pljava to /opt/pljava
  * sudo vi /etc/postgresql/8.3/main/postgresql.conf

<code>
SET the following parameters:
    dynamic_library_path = '$libdir:/opt/pljava'
    log_min_messages = info   #optional
    custom_variable_classes = 'pljava'

ADD the following parameters:
    pljava.classpath = '/opt/pljava/pljava.jar'
</code>
* update ldconfig so postgres finds the reqired shared objects. Create a file **/etc/ld.so.conf.d/pljava.conf**  containing

 . /usr/lib/jvm/java-6-sun/jre/lib/amd64 /usr/lib/jvm/java-6-sun/jre/lib/amd64/server /usr/lib/jvm/java-6-sun/jre/lib/amd64/native_threads
  * run

<code bash>
  sudo ldconfig
</code>
* install pl/java

<code bash>
    sudo su postgres
    psql -f install.sql
</code>
===== PL/R =====
==== Installing ====
* Install the postgres-plr package

 . sudo aptitude install postgresql-8.3-plr
  * On ubuntu 8.04 the packet does not exist but the Jaunty Version works just fine. Search for the correct package on http://packages.ubuntu.com/search?keywords=postgresql-8.3-plr then do something like

<code>
   wget http://de.archive.ubuntu.com/ubuntu/pool/universe/p/plr/postgresql-8.3-plr_8.3.0.6-3_amd64.deb
   dpkg -i postgresql-8.3-plr_8.3.0.6-3_amd64.deb
</code>
* Install PL/R in the database of choice

<code>
   psql <dbname> < /usr/share/postgresql/8.3/plr.sql
</code>
* Test

<code sql>
  CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
    if (arg1 > arg2)
       return(arg1)
    else
       return(arg2)
  ' LANGUAGE 'plr' STRICT;

  SELECT r_max(10,20);
</code>
==== Using ====
Have a look at the guide here http://www.joeconway.com/plr/doc/index.html

====== Useful commands ======
===== Inserting from CSV Files =====
To efficiently insert a lot of records from csv-files into the table "tolldata" use the [[http://www.postgresql.org/docs/9.1/static/sql-copy.html]] COPY command:

<code bash>
cat *.csv | psql -c "COPY tolldata FROM stdin WITH DELIMITER ';'" -d toll_tst -U toll -h localhost
</code>
Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must //reside on or be accessible to the database server machine//, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

===== Outputting in CSV Format =====
If you need to export an entire table

<code>
COPY table TO '/tmp/table.csv' DELIMITER ',' CSV HEADER;
</code>

If query is more complicated: 

<code sql>
--- toggle to unaligned output mode
\a
--- set ; as field delimiter
\f ;
--- disable the footer (row count)
\pset footer off

SELECT * FROM table \g file.csv
</code>
===== Piping query result to file =====
In psql command line:

<code sql>
SELECT * FROM table \g filename
</code>
([[http://archives.postgresql.org/pgsql-general/2001-12/msg00687.php see more details]])

===== Show Tables =====
<code sql>
  \dt
or
  \dt my_schema
</code>
===== drop all tables in a schema =====
<code sql>
 select 'drop table '|schemaname|'.'|tablename|';' from pg_tables where schemaname in ('public') order by schemaname, tablename;
</code>
===== drop all sequences in a schema =====
<code sql>
 select 'drop sequence public.'|sequence_name|';' from information_schema.sequences where sequence_schema in ('public') order by  sequence_name;
</code>
===== Grant on all tables =====
<code sql>
 select 'grant select, insert, update, delete on ' | tablename | ' to muh;' from pg_tables where schemaname = 'public';
</code>

====== Backups ======

It makes sense to use the postgres user
<code bash>
sudo su postgres
</code>

There are two important variants of making backups:

Create and restore a backup using plain-text sql: ''pg_dump'' and ''psql''
<code bash>
pg_dump -U user source_db -f db_backup.sql

# restore
createdb target_db
psql -U user -d target_db -f db_backup.sql
</code>

Create and restore a backup using the compressed 'custom' format: ''pg_dump'' and ''pg_restore''
<code bash>
pg_dump -Fc -U user source_db > db_backup.dmp

# restore (requires an empty database)
createdb target_db
pg_restore -Fc -d target_db db_backup.dmp
</code>

In addition we can use gzip (and zcat) to further compress the compressed 'custom' format and give the dump a nice name:
<code bash>
pg_dump -Fc -U user source_db | gzip > "$(date +%Y-%m-%d)_db_backup.dmp.gz"
zcat YYYY-MM-DD_db_backup.dmp.gz | pg_restore -Fc -d target_db
</code>

Or, when creating the backup via cronjob in ''/etc/cron.d'':
<code>
  0   5   *   *   *   postgres  pg_dump -Fc dbname | gzip > /path/to/db_backup_`date +\%Y-\%m-\%dT\%H_\%M`.dmp.gz
# ┬   ┬   ┬   ┬   ┬   ┬         ┬
# │   │   │   │   |   │         └ command
# │   │   │   │   |   └───── user executing the command
# │   │   │   │   └───────── day of week (0 - 7) (0 to 7 are Sunday to Sunday, or use names)
# │   │   │   └───────────── month (1 - 12)
# │   │   └───────────────── day of month (1 - 31)
# │   └───────────────────── hour (0 - 23)
# └───────────────────────── min (0 - 59)
</code>
====== Performance Tuning ======

[[Monitoring Server Activity]]
===== References =====
  * See http://samiux.wordpress.com/2009/07/26/howto-performance-tuning-for-postgresql-on-ubuntudebian/ for a good introduction and some reasonable default values. 
  * See http://www.postgresql.org/docs/8.4/interactive/kernel-resources.html#SYSVIPC for Kernel Parameters that may be relevant when tuning Postgres Performance on Linux Systems
  * Note that while SHMMAX is measured in bytes SHMALL is measured in **Pages**
    * The default page size on a 32bit Linux system is usually 4096 bytes (check with the command //getconf PAGE_SIZE//). So if you change SHMMAX you should also check that // SHMALL >= SHMMAX / PAGE_SIZE //
Small heaps of code

User Tools

Site Tools

Page Tools
