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

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.

Configuration files

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

Basic Commands

Binaries: psql, pg_dump.

  • Connect to a PostgreSQL database: psql -h localhost -U postgres -d somedb[1]
  • Connect to a PostgreSQL database with a timeout of 5 seconds: env PGCONNECT_TIMEOUT=5 psql -h localhost -U postgres -d somedb[2]
  • Show databases: SELECT datname FROM pg_database WHERE datistemplate = false;
  • Show tables: \dt or SELECT * FROM pg_catalog.pg_tables;[3]
  • Drop DB: dropdb DB_NAME
  • Describe employees table: \d employees
  • Show/List schemas: select nspname from pg_catalog.pg_namespace;[4]
  • Show version: SELECT version();
  • Create backup: use pg_dump [5] 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

  • wal writer process

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: