PostgreSQL

From wikieduonline
Jump to navigation Jump to search

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.


Configuration files[edit]

/etc/postgresql/10/main/pg_hba.conf


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

Basic Commands[edit]

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

  • wal writer process

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