PostgreSQL

From Dogtag
Jump to: navigation, search

Installation

To install PostgreSQL client:

$ dnf install postgresql

To install PostgreSQL server:

$ dnf install postgresql-server

Configuration

The configuration files are located at:

  • /var/lib/pgsql/data/postgresql.conf
  • /var/lib/pgsql/data/pg_hba.conf

To enable remote access, edit /var/lib/pgsql/data/postgresql.conf as follows:

listen_addresses = '*'

and edit /var/lib/pgsql/data/pg_hba.conf as follows:

#host    all             all             127.0.0.1/32            ident
host    all             all             127.0.0.1/32            md5

#host    all             all             ::1/128                 ident
host    all             all             ::1/128                 md5

host    all             all             0.0.0.0/0               md5

then restart the server:

$ systemctl restart postgresql

Initializing Database

To initialize PostgreSQL:

$ postgresql-setup --initdb --unit postgresql

Systemd Service

To start the PostgreSQL service:

$ systemctl start postgresql

To stop the PostgreSQL service:

$ systemctl stop postgresql

Connecting to Database

To connect to a local database as a root user:

$ sudo -u postgres psql -d <database>

To connect to a local database as a normal user:

$ sudo -u <username> psql -d <database>

To connect to a remote database:

$ psql -h <hostname> -p <port> -d <database> -U <username>

or

$ psql postgres://<username>:<password>@<hostname>:<port>/<database>

Managing Users

To list users:

psql> \du

To create a user:

psql> create user <username> with password '<password>';

To drop a user:

psql> drop user <username>;

Managing Databases

To list databases:

psql> \l

To create a database:

psql> create database <datatabase> owner <username>;

To show the current database:

psql> select current_database();

To switch to another database:

psql> \c <database>

To drop a database;

psql> drop database <database>;

Listing Tables

psql> \dt

Describing a Table

psql> \d <name>

Container

To run PostgreSQL container:

$ podman run \
    --name postgres \
    --rm \
    --network example \
    -e POSTGRES_PASSWORD=Secret.123 \
    -it \
    postgres

See Also