PostgreSQL
April 29, 2015
postgres postgresql

Installation 

1

brew install postgres

Start

To have launchd start postgresql now and restart at login:

1

brew services start postgresql

Or, if you don’t want/need a background service you can just run:

1

pg_ctl -D /usr/local/var/postgres start

in Debian

1

sudo /etc/init.d/postgresql start

JDBC Connector

JDBC connector

Import database 

1

psql -U username dbname < load.sql

Export database 

1

pg_dump -U username dbname > load.sql

You may receive the following error messages when you try to export a database:

1
2
3

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  permission denied for schema topology
pg_dump: The command was: LOCK TABLE topology.topology IN ACCESS SHARE MODE

These errors occur because some server database templates include PostGIS with restricted access permissions. To export a PostgreSQL database without this data, type the following command:

1

pg_dump -U username dbname -N topology -T spatial_ref_sys > dbexport.pgsql

Force drop db while others may be connected

Connect to your server as superuser, using psql or other client. Do not use the database you want to drop.

1

$ psql -h localhost postgres postgres

Step 1. Make sure no one can connect to this database. You can use one of following methods (the second seems safer, but does not prevent connections from superusers).

1
2
3
4
5

#Method 1: update system catalog
$ psql -U postgres -c "UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';"

#Method 2: use ALTER DATABASE. Superusers still can connect!
$ psql -U postgres -c "ALTER DATABASE mydb CONNECTION LIMIT 0;"

Step 2. Force disconnection of all clients connected to this database, using pg_terminate_backend.

1

$ psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';"

Step 3. Drop the database.

1

$ psql -U postgres -c "DROP DATABASE mydb;"

Step 1 requires superuser privileges for the 1st method, and database owner privileges for the 2nd one. Step 2 requires superuser privileges. Step 3 requires database owner privilege.

Step 4. Drop the user.

1

$ psql -U postgres -c "DROP USER mydbuser;"

Useful Commands 

1
2
3
4
5

$ createuser --interactive -P
postgres=# create user <user>;

$ createdb --owner=postgres --encoding=utf8 user
postgres=# create database <database>;

By default, postgres tries to connect to a database with the same name as your user. To prevent this default behaviour, just specify user and database:

1
2
3

$ createdb <user>
psql -d <user>
psql -U Username DatabaseName

Database size and data occupied size

To get the table size and the database size

1

SELECT pg_size_pretty(pg_total_relation_size('"<schema>"."<table>"')), pg_size_pretty(pg_database_size('dbname'));

To get the list of all databases with their respective size

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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
    LIMIT 20;

To get the list of all tables with their respective size

1
2
3
4
5
6

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

To get the list of all tables with their respective size as table, indexes and total size

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

To calculate row size

1
2
3

SELECT pg_size_pretty(sum(pg_column_size(t.*))) as filesize, count(*) as filerow FROM tablename as t

SELECT pg_size_pretty(sum(pg_column_size(t.*))) as filesize, count(*) as filerow FROM tablename as t WHERE t.id=12345

To calculate field size

1
2
3

select pg_size_pretty(sum(pg_column_size(t.fieldname))) as row_size from tablename as t;

select pg_size_pretty(sum(pg_column_size(t.fieldname))) as row_size from tablename as t where id = 1;

Migration from mysql to postgres using pgloader

pgloader is a data loading tool for PostgreSQL.

1
2
3

brew install pgloader
pgloader mysql://username_mysql:P@@ssword1@localhost:3306/dbname_mysql pgsql://username_pssql@localhost:5432/dbname_pssql
pgloader pgload_test.load

Below is the sample pgload_test.load file

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

LOAD DATABASE
    FROM mysql://username_mysql:P@@ssword1@localhost:3306/dbname_mysql
    INTO pgsql://username_pssql@localhost:5432/dbname_pssql

#WITH include drop, create tables
WITH include no drop, truncate, disable triggers, create no tables,
     create no indexes, preserve index names, no foreign keys,
     data only

ALTER SCHEMA 'dbname_mysql' RENAME TO 'public'
;

GUI 

1
2
3
4
5

brew cask install pgadmin4
brew cask install postico
brew cask install dbeaver-community
brew cask install navicat-premium
brew cask install sqlworkbenchj

Errors

org.postgresql.xa.PGXAException: Error rolling back prepared transaction.

While commiting a distributed trasaction in Glassfish / PostgreSQL combination, you may get the follwoing error.

1
2
3

java.lang.RuntimeException: org.postgresql.xa.PGXAException: Error preparing transaction] on Resource [prepare] operation.

javax.enterprise.system.core.transaction.com.sun.jts.jtsxa|_ThreadID=22;_ThreadName=Thread-4;|JTS5068: Unexpected error occurred in rollback org.postgresql.xa.PGXAException: Error rolling back prepared transaction

The solution is:

  • Search for postgresql.conf in PostgreSQL installation folder. Generally it will be available in [PostgreSQL installation folder]\data\
  • Open that file in a text editor and search for max_prepared_transactions.
  • If that property is commented (#max_prepared_transactions), then remove # symbol and enter some value (say 20).
  • Save that file
  • Restart PostgreSQL