PostgreSQL
PostgreSQL cheat sheet: installation, import and export, useful psql queries and migrating from MySQL.
4 min read
Installation
brew install postgresStart
To have launchd start postgresql now and restart at login:
brew services start postgresqlOr, if you don’t want/need a background service you can just run:
pg_ctl -D /usr/local/var/postgres startin Debian
sudo /etc/init.d/postgresql startJDBC Connector
Import database
psql -U username dbname < load.sqlExport database
pg_dump -U username dbname > load.sqlYou may receive the following error messages when you try to export a database:
pg_dump: SQL command failedpg_dump: Error message from server: ERROR: permission denied for schema topologypg_dump: The command was: LOCK TABLE topology.topology IN ACCESS SHARE MODEThese 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:
pg_dump -U username dbname -N topology -T spatial_ref_sys > dbexport.pgsqlForce 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.
$ psql -h localhost postgres postgresStep 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).
#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.
$ psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';"Step 3. Drop the database.
$ 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.
$ psql -U postgres -c "DROP USER mydbuser;"Useful Commands
$ createuser --interactive -Ppostgres=# create user <user>;
$ createdb --owner=postgres --encoding=utf8 userpostgres=# 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:
$ createdb <user>psql -d <user>psql -U Username DatabaseNameDatabase 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 SizeFROM 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 sizeFROM information_schema.tablesORDER 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_sizeFROM ( 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=12345To 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.
brew install pgloaderpgloader mysql://username_mysql:P@@ssword1@localhost:3306/dbname_mysql pgsql://username_pssql@localhost:5432/dbname_pssqlpgloader pgload_test.loadBelow 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 tablesWITH 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
brew cask install pgadmin4brew cask install posticobrew cask install dbeaver-communitybrew cask install navicat-premiumbrew cask install sqlworkbenchjErrors
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 transactionThe solution is:
- Search for
postgresql.confin 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