---
title: "[PostgreSQL](https://en.wikipedia.org/wiki/PostgreSQL)"
weight: 2
---

- <https://www.postgresql.org/>
- <https://help.ubuntu.com/community/PostgreSQL>

## Server Installation

```text
$ 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

```text
$ sudo -u postgres psql  # execute the "\password" command
```

```text
$ sudo cp -p /etc/postgresql/9.3/main/postgresql.conf{,.orig}
$ sudo joe /etc/postgresql/9.3/main/postgresql.conf
```

```text
$ sudo service postgresql reload
```

## Server Monitoring

```text
$ tailf /var/log/postgresql/postgresql-9.3-main.log
```

## Database Administration

```text
$ sudo -u postgres psql -h localhost  # to connect over network socket
```

```text
$ sudo -u postgres createdb mydb  # to create a database
$ sudo -u postgres psql mydb
$ sudo -u postgres dropdb mydb    # to drop a database
```

```text
$ sudo -u postgres pg_dump -c mydb | xz -9 > mydb.sql.xz
```

## Reference

### Manual Pages

- [createdb(1)](http://www.postgresql.org/docs/9.3/static/app-createdb.html)
- [createuser(1)](http://www.postgresql.org/docs/9.3/static/app-createuser.html)
- [dropdb(1)](http://www.postgresql.org/docs/9.3/static/app-dropdb.html)
- [dropuser(1)](http://www.postgresql.org/docs/9.3/static/app-dropuser.html)
- [pg_config(1)](http://www.postgresql.org/docs/9.3/static/app-pgconfig.html)
- [pg_dump(1)](http://www.postgresql.org/docs/9.3/static/app-pgdump.html)
- [pg_dumpall(1)](http://www.postgresql.org/docs/9.3/static/app-pg-dumpall.html)
- [pg_restore(1)](http://www.postgresql.org/docs/9.3/static/app-pgrestore.html)
- [psql(1)](http://www.postgresql.org/docs/9.3/static/app-psql.html)
- [reindexdb(1)](http://www.postgresql.org/docs/9.3/static/app-reindexdb.html)
- [vacuumdb(1)](http://www.postgresql.org/docs/9.3/static/app-vacuumdb.html)

### [Extensions](https://www.postgresql.org/docs/current/static/contrib.html)

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

### Languages

- [PL/pgSQL](https://www.postgresql.org/docs/current/static/plpgsql.html)
- [PL/Lua](http://pllua.projects.pgfoundry.org/)

### Bindings

- [C++ (libpqxx)](https://github.com/jtv/libpqxx)

## See Also

- [PostGIS](postgis)
