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