PostgreSQL

From wikieduonline
Revision as of 14:13, 3 November 2022 by Ant (talk | contribs) (→‎Related terms)
Jump to navigation Jump to search

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

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.

Docker

docker run -p 5432:5432 --name MY_POSTGRES_DB -e POSTGRES_PASSWORD=mysecretpassword -d postgres[1]
* 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

PostgreSQL

bitnami/postgresql
bitnami/postgresql-ha

Configuration and data files

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

Data:

PostgreSQL logs

Basic Commands

Binaries: psql, pg_dump.

  • Connect to a PostgreSQL database: psql -h localhost -U postgres -d somedb[2]
  • Connect to a PostgreSQL database with a timeout of 5 seconds: env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb[3]
  • Show databases:
    • \list
    • SELECT datname FROM pg_database WHERE datistemplate = false;
  • Show tables: \dt or SELECT * FROM pg_catalog.pg_tables;[4]
  • Show users: \du
  • Drop DB: dropdb DB_NAME
  • Create backup: use pg_dump [6] 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;



Processs

  • wal writer process

Related terms

Activities

See also

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

Advertising: