Skip to content

PostgreSQL

PostgreSQL cheat sheet: installation, import and export, useful psql queries and migrating from MySQL.

4 min read

Installation

Terminal window
brew install postgres

Start

To have launchd start postgresql now and restart at login:

Terminal window
brew services start postgresql

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

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

in Debian

Terminal window
sudo /etc/init.d/postgresql start

JDBC Connector

JDBC connector

Import database

Terminal window
psql -U username dbname < load.sql

Export database

Terminal window
pg_dump -U username dbname > load.sql

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

Terminal window
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:

Terminal window
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.

Terminal window
$ 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).

Terminal window
#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.

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

Step 3. Drop the database.

Terminal window
$ 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.

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

Useful Commands

Terminal window
$ 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:

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

Database size and data occupied size

To get the table size and the database size

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

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

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

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

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

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.

Terminal window
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

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

Terminal window
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.

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

Related articles

1 min read

SQL

SQL joins explained — inner, left, right and full outer — with examples.

  • Databases
1 min read

Database Migration

Migrating a MySQL database to PostgreSQL with pgloader, step by step.

  • Databases
2 min read

JDBC

JDBC connection pools, driver and DataSource classes per database, and local versus XA transactions.

  • Java & Jakarta EE
  • Databases

Type to search the whole site.