Installation
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