= PostgreSQL =

'''PostgreSQL''' is a relational database. It was designed as the successor to Ingress.

<<TableOfContents>>

----



== Installation ==

Most [[Linux]] and [[BSD]] distributions offer a `postgresql` package that contains the core server components.



=== Containers ===

[[Docker]] container images are also available for all supported versions. The images are available from [[Docker/Hub|DockerHub]] as `docker.io/library/postgres` (or simply `postgres` when using `docker(1)` specifically).

Try:

{{{
sudo docker run --detach --name my-postgres \
  --env POSTGRES_PASSWORD=my-secret-passwd \
  postgres:latest
}}}

Best practice is to set the `POSTGRES_PASSWORD` environment variable instead of using the password as plaintext.

{{{
sudo docker run --detach --name my-postgres \
  --env POSTGRES_PASSWORD \
  postgres:latest
}}}

It will likely be necessary to also [[Docker/BindMounts|bind mount]] directories for configuration files and the database file. 

{{{
sudo docker run --detach --name my-postgres \
  --env POSTGRES_PASSWORD=my-secret-passwd \
  --env PGDATA=/var/lib/postgresql/data/pgdata \
  --mount type=bind,src=/path/to/data/dir,dst=/var/lib/postgresql/data \
  postgres:latest
}}}

----



== Setup ==

Before using `postgres(1)`, the database cluster needs to be instantiated.

{{{
initdb
}}}

For most usecases, this is sufficient. See [[PostgreSQL/InitDB|here]] for details and more advanced usage.

For `systemd`-capable systems, [[Linux/Systemd|start and enable]] `postgres.service`.

For [[BSD]] distributions, try:

{{{
pg_ctl -l logfile start
}}}

To launch the database on startup, update `/etc/rc.conf`:

{{{
postgresql_enable=yes
}}}

A database can now be created with the `createdb(1)` utility. Try:

{{{
createdb dbname --username=postgres
}}}

To create a user role other than the default `postgres` user, try:

{{{
createuser --interactive
}}}

A database can then be created with that alternate user as the owner with:

{{{
createdb dbname --owner=username
}}}

----



== Configuration ==

Configurations are primarily made with the configuration file. The file should consist of lines like `parameter = value`. The `=` is optional. Anything following a `#` is a comment.

Configurations can also be made with command line options (such as `postgres -c log_connections=on`). Parameters use the same names as the configuration file.

In either case, configurations are set at startup. To make a change, either restart the cluster or use `pg_ctl reload`.

Some parameters can be changed at run time with the `SET` SQL command.

See [[PostgreSQL/Configuration|here]] for details on configuration.

----



== Usage ==

The `psql(1)` utility is a convenient terminal client. See [[PostgreSQL/Psql|here]] for details on how to use it.

The connection string is structured as `postgresql://postgres@localhost:5432/dbname?sslmode=disable`. Naturally, if the connection should be encrypted, do not include the `sslmode=disable` component.

----



== See also ==

[[https://www.postgresql.org/docs/current/index.html|PostgreSQL documentation]]

[[PostgreSQL/Configuration|PostgreSQL configuration]]

[[PostgreSQL/InitDB|initdb(1)]]

[[PostgreSQL/Psql|psql(1)]]



----
CategoryRicottone