Arto's Notes re:
PostgreSQL
Server Installation
$ aptitude install postgresql postgresql-contrib # on Ubuntu
$ brew install postgresql --without-perl --without-tcl
$ brew services start postgresql
$ pg_ctl -D /opt/homebrew/var/postgres start
$ pg_ctl -D /opt/homebrew/var/postgres stop
$ createdb `whoami`
$ psql `whoami`
$ brew services restart postgresql
Server Configuration
$ sudo -u postgres psql # execute the "\password" command
$ sudo cp -p /etc/postgresql/9.3/main/postgresql.conf{,.orig}
$ sudo joe /etc/postgresql/9.3/main/postgresql.conf
$ sudo service postgresql reload
Server Monitoring
$ tailf /var/log/postgresql/postgresql-9.3-main.log
Database Administration
$ sudo -u postgres psql -h localhost # to connect over network socket
$ sudo -u postgres createdb mydb # to create a database
$ sudo -u postgres psql mydb
$ sudo -u postgres dropdb mydb # to drop a database
$ sudo -u postgres pg_dump -c mydb | xz -9 > mydb.sql.xz
Reference
Manual Pages
- createdb(1)
- createuser(1)
- dropdb(1)
- dropuser(1)
- pg_config(1)
- pg_dump(1)
- pg_dumpall(1)
- pg_restore(1)
- psql(1)
- reindexdb(1)
- vacuumdb(1)
Extensions
- https://www.postgresql.org/docs/current/static/auto-explain.html
- https://www.postgresql.org/docs/current/static/bloom.html
- https://www.postgresql.org/docs/current/static/btree-gin.html
- https://www.postgresql.org/docs/current/static/btree-gist.html
- https://www.postgresql.org/docs/current/static/citext.html
- https://www.postgresql.org/docs/current/static/file-fdw.html
- https://www.postgresql.org/docs/current/static/pgcrypto.html
- https://www.postgresql.org/docs/current/static/postgres-fdw.html
- https://www.postgresql.org/docs/current/static/seg.html
- https://www.postgresql.org/docs/current/static/contrib-spi.html
- https://www.postgresql.org/docs/current/static/uuid-ossp.html