PostgreSQL

• PostgreSQL connect to database using psql
• List all databases in PostgreSQL
• pg_ctl
• Creating user in PostgreSQL
• PostgreSQL Create Database
• PostgreSQL Delete a Database
• Backup PostgreSQL Database
• PostgreSQL Export table into CSV file
• Run PostgreSQL in Docker
• postgresql.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files
• https://www.pgbouncer.org/

psql commands

To install PostgreSQL on Ubuntu/Debian, run

apt-get install -y postgresql

Start it with

service postgresql start

By default user “postgres” have full access on PostgreSQL. To create a user, run

su postgres
createuser -P USERNAME

To create a database, run

createdb -T template0 -E UTF8 -O USERNAME DBNAME

You can see help with

postgres@ok-vm:~$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@postgresql.org>.
postgres@ok-vm:~$ 

run psql

sudo -u postgres psql

Run a command in psql

sudo -u postgres psql -c "\du"

List all tables in a database

sudo -u postgres psql -c "\c serverok_db" -c "\dt"

List postgresql extensions

sudo -u postgres psql -c "SELECT * FROM pg_available_extensions;"

Extensions are stored in folder /usr/share/postgresql/16/extension/