|
Size: 1765
Comment:
|
← Revision 18 as of 2023-04-08 22:37:41 ⇥
Size: 3372
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 15: | Line 15: |
| [[Docker]] container images are also available for all supported versions. The image is available from [[Docker/Hub|DockerHub]] as `docker.io/library/postgres` (or simply `postgres` when using `docker(1)` specifically). | === 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 }}} |
| Line 30: | Line 60: |
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 }}} |
|
| Line 57: | Line 119: |
| ---- == See also == [[https://www.postgresql.org/docs/current/index.html|PostgreSQL documentation]] [[PostgreSQL/Configuration|PostgreSQL configuration]] [[PostgreSQL/InitDB|initdb(1)]] [[PostgreSQL/Psql|psql(1)]] |
PostgreSQL
PostgreSQL is a relational database. It was designed as the successor to Ingress.
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 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 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 here for details and more advanced usage.
For systemd-capable systems, 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 here for details on configuration.
Usage
The psql(1) utility is a convenient terminal client. See 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.