Index

Postgres

installing postgres
sudo apt-get install postgresql

Configuring Postgres

changing the maximum allowed connections
sudo vi /etc/postgresql/9.3/main/postgresql.conf
max_connections = 500
ip whitelisting
sudo vi /etc/postgresql/9.3/main/pg_hba.conf
host    all             all             10.10.10.10/32        md5

Postgres in terminal

becoming the postgres user
sudo su postgres
postgres terminal:
psql
postgres terminal connected to database [dbname]
psql [dbname]
connecting to a database on login:
psql -h localhost -p 5432 -U [user] [database]
connect to remote database
psql -h [url] -p [port] [database] [username]
running a command on a database from the command line
psql -d [database] -c "DROP SCHEMA public CASCADE;"
create users/ databases:
createuser -P -e [user]
createdb -O [user] [dbname]

Database Dumps

reading an sql file
psql -d [database] -a -f [file]
creating a database text dump file
pg_dump [dbname] > outfile
reading a database text dump file
psql [dbname] < infile
pg_dump flags
-a data only
-f output file
-n schema
-t table
-v verbose
-- inserts (sql insert statements)

-h host
-p port
-U username
reading a database dump
sudo -u postgres pg_restore -d [database] "/tmp/dump.backup"

Postgres Command Line

help
help
postgres help
\?
SQL help
\h
quit
\q
show database in console:
\l
connect to database
\c [database]
show tables, views, sequences
\d
describe table
\d [table]
show tables only
\dt
show sequences only
\ds
show views only
\dv
execute queries from file
\i [FILE]
output query results to file
\o [FILE]

Java JDBC driver

Get Datasource
PGPoolingDataSource source = new PGPoolingDataSource();
source.setDataSourceName("postgre");
source.setServerName("localhost");
source.setDatabaseName("example");
source.setUser("example");
source.setPassword("example");
source.setMaxConnections(10);