PostgreSQL

wikipedia:PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS). PostgreSQL is ACID-compliant and Transactional. PostgreSQL has updatable Views and Materialized view, Triggers, Foreign key; supports functions and stored procedures, and other expandability. PostgreSQL listens by default in TCP port 5432.

Installing PostgreSQL

MacOS

• brew install postgres: pg_ctl and other 33 binaries.
• brew install libpq: psql and other 28 binaries.

Linux, BSD variants

The easiest way to install PostgreSQL in these operating systems is to use their respective built-in package managers - for instance, the pkg command in DragonFlyBSD or apt in Debian and its derivatives. Be advised that there will be different variants of PostgreSQL available for install at a given point, specially regarding version numbers and client/server side. You should decide beforehand which version suits your needs for a particular application. Furthermore, you need to make sure that you have sufficient privileges in the system to install software (properly configured sudo/doas access, for example) or your install will fail.

• Ubuntu: apt install postgresql
• Amazon AMI: apt install postgresql-server

Docker

docker run -p 5432:5432 --name MY_POSTGRES_DB -e POSTGRES_PASSWORD=mysecretpassword -d postgres
* Docker-compose.xml PostgreSQL

docker run -p 5432:5432 -d \
   --name some-postgres \
   -e POSTGRES_PASSWORD=mysecretpassword \
   -e PGDATA=/var/lib/postgresql/data/pgdata \
   -v /custom/mount:/var/lib/postgresql/data \
   postgres

image: arm64v8/postgres

• Binaries: pg_isready, psql, pg_restore

PostgreSQL

• bitnami/postgresql
• bitnami/postgresql-ha

Configuration and data files

• /etc/postgresql/*/main/postgresql.conf
• /etc/postgresql/*/main/pg_hba.conf (PostgreSQL Client Authentication Configuration File)

# "local" is for Unix domain socket connections only
local   all             all                                     md5

• Data: /var/lib/postgresql/

PostgreSQL logs

• /var/log/postgresql/

Basic Commands

Binaries: psql, pg_dump.

• Connect to a PostgreSQL database: psql -h localhost -U postgres -d somedb
• Connect to a PostgreSQL database with a timeout of 5 seconds: env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb
• Show databases:
  • \list
  • SELECT datname FROM pg_database WHERE datistemplate = false;
• Show tables: \dt or SELECT * FROM pg_catalog.pg_tables;
• Show users: \du
• Drop DB: dropdb DB_NAME

• Describe employees table: \d employees
• Show/List schemas: select nspname from pg_catalog.pg_namespace;
• Show version: SELECT version();

• Create backup: use pg_dump and ~/.pgpass file for automating login.
• Create user:
  • create user <username>
  • ALTER USER user_name WITH PASSWORD 'new_password';
• create database
• Modify user privileges:
  • Privileges to create DB: ALTER USER user1 CREATEDB;

• systemctl [ status | stop | start ] postgresql

• \l, \du, \dt, \c, \di

Processs

• wal writer process

Related terms

• Role, Schema
• pg_upgradecluster
• Adminer
• Amazon Redshift
• PostgreSQL monitoring
  • PostgreSQL statistics collector: pg_stat*
  • prometheus-postgres-exporter
• Multi-Version Concurrency Control (MVCC)
• Index (PostgreSQL)
• Greenplum
• PostgreSQL replication
• PGO PostgreSQL Operator
• Amazon Aurora PostgreSQL Serverless
• PgBouncer connection pooler
• Amazon RDS for PostgreSQL
• Master user for RDS: postgres
• Ansible: community.postgresql.postgresql_query
• psycopg2
• PostgreSQL logging
• yum install postgresql
• arm64v8/postgres
• Azure Cosmos DB for PostgreSQL
• Azure Database for PostgreSQL

Activities

• PostgreSQL parameter tunning
• Read PostgreSQL - How to grant access to users? https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html
• Operations

See also

• Patroni HA solution for PostgreSQL that requires etcd, Zookeeper, or Consul
• PostgreSQL, PostgreSQL extensions, CREATE EXTENSION, List extensions (\dx)
• PostgreSQL: pg_dump, pg_restore, pgAdmin, pg_config, psql, pg_ctl, pg_isready, initdb, Role, Schema, DBeaver, Navicat, DataGrip, OmniDB, Adminer, docker-compose.xml PostgreSQL, PostgreSQL version, PostgreSQL logs, postgresql.conf, pg_hba.conf, $HOME/.pg_service.conf, Create database (PostgreSQL), Create user, createdb, GRANT, pg_stat, PostgreSQL VACUUM, EXPLAIN, pg stat activity, Autovacuum, ALTER DATABASE, PostgreSQL statistics collector, Shared buffers, EXPLAIN (PostgreSQL), EXPLAIN ANALYZE, Bitmap scan, EXPLAIN VERBOSE, EXPLAIN VERBOSE (PostgresSQL), WAL, ALTER USER, CREATE ROLE, CREATE USER, \du, show users, \l, The Statistics Collector, pganalyze, cron.schedule, Master, pg_tables, PostgreSQL replication, CREATE, SET, TOAST, PgBouncer, Restore DB, Index, meta-commands, Table, foreign table, Schema, Sequence, Views, materialized view, Table Partitioning, monitoring, PostgreSQL System Administration Functions, PostgreSQL extension, privileges, logging, PGTune, PostgreSQL parameter tunning, PostgreSQL modules, pgbench, PostgreSQL users, catalogs

Text is available under the Creative Commons Attribution-ShareAlike License; additional terms may apply. By using this site, you agree to the Terms of Use and Privacy Policy.

Source: wikiversity