PostgreSQL

From wikieduonline
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[edit]

MacOS[edit]

Linux, BSD variants[edit]

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[edit]

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

Configuration files[edit]

/etc/postgresql/10/main/postgresql.conf
/etc/postgresql/10/main/pg_hba.conf (PostgreSQL Client Authentication Configuration File)
# "local" is for Unix domain socket connections only
local   all             all                                     md5

PostgreSQL logs[edit]

Basic Commands[edit]

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: 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
  • Describe employees table: \d employees
  • Show/List schemas: select nspname from pg_catalog.pg_namespace;[5]
  • Show version: SELECT version();
  • Create backup: use pg_dump [6] and ~/.pgpass file for automating login.
  • Create user:
    • createuser <username>
    • ALTER USER user_name WITH PASSWORD 'new_password';
  • create database
  • Modify user privileges:
    • Privileges to create DB: ALTER USER user1 CREATEDB;


Processs[edit]

  • wal writer process


Related terms[edit]

Activities[edit]

See also[edit]

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