*ARGS.TECH | BLOG | Install and configure PostgreSQL in Debian-like systems

BLOG

Install and configure PostgreSQL in Debian-like systems

PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
There is a wealth of information to be found describing how to install and use PostgreSQL through the official documentation.

Install in Debian, Ubuntu:

sudo apt install -y postgresql

By default PostgreSQL listen 5432 port. Check installation was successful with sudo netstat -tlnp | grep 5432 command. You must see output like this:

[user@localhost ~]$ sudo netstat -tlnp | grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1761/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 1761/postgres

Postgresql have command line client named as "psql" for extracting queries. Enter in psql cli:

sudo -u postgres psql

Here "-u postgres" means "run command (or edit file) as specified user name or ID". Above command open psql as postgres user.

Create new database:

postgres=# CREATE DATABASE my_database;

To create a database, you must be a superuser or have the special CREATEDB privilege.
By default, the new database will be created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE name. In particular, by writing TEMPLATE template0, you can create a pristine database (one where no user-defined objects exist and where the system objects have not been altered) containing only the standard objects predefined by your version of PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that might have been added to template1. Documentation.

Drop database:

postgres=# DROP DATABASE my_database;

DROP DATABASE drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. It cannot be executed while you are connected to the target database. (Connect to postgres or any other database to issue this command.) Also, if anyone else is connected to the target database, this command will fail unless you use the FORCE option described below.
DROP DATABASE cannot be undone. Use it with care! Documentation.

Create new user:

postgres=# CREATE USER myuser WITH PASSWORD 'password';

CREATE USER adds a new user to a PostgreSQL database cluster. You must be a database superuser to use this command. Documentation.

Change user password:

postgres=# ALTER USER myuser WITH PASSWORD 'new_password';

ALTER USER changes the attributes of a PostgreSQL user account. Attributes not mentioned in the command retain their previous settings.
The first variant of this command listed in the synopsis changes certain per-user privileges and authentication settings. (See below for details.) Database superusers can change any of these settings for any user. Ordinary users can only change their own password.
The second variant changes the name of the user. Only a database superuser can rename user accounts. The current session user cannot be renamed. (Connect as a different user if you need to do that.) Because MD5-encrypted passwords use the user name as cryptographic salt, renaming a user clears their MD5 password.
The third and the fourth variant change a user's session default for a specified configuration variable. Whenever the user subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postmaster command line. Ordinary users can change their own session defaults. Superusers can change anyone's session defaults. Certain variables cannot be set this way, or can only be set by a superuser. Documentation.

Delete user:

postgres=# DROPUSER newuser;

DROPUSER removes an existing PostgreSQL user. Only superusers and users with the CREATEROLE privilege can remove PostgreSQL users. (To remove a superuser, you must yourself be a superuser.)
DROPUSER is a wrapper around the SQL command DROP ROLE. There is no effective difference between dropping users via this utility and via other methods for accessing the server. Documentation.

Grant privileges:

postgres=# GRANT ALL PRIVILEGES ON DATABASE my_database TO myuser;

The GRANT command has two basic variants: one that grants privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, large object, configuration parameter, schema, tablespace, or type), and one that grants membership in a role. These variants are similar in many ways, but they are different enough to be described separately. Documentation.

Example of SELECT query. SELECT retrieves rows from zero or more tables. Simple SELECT query which return all rows from "users" table:

postgres=# SELECT * FROM users;

If you want filter results use WHERE condition. Next query return user with "admin" username:

postgres=# SELECT * FROM users WHERE username = 'admin';

INSERT inserts new rows into a table. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query. Insert new row:

postgres=# INSERT INTO users (username, email) VALUES ('johndoe', 'johndoe@example.com');

UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values. Update row:

postgres=# UPDATE users SET column1 = value1, column2 = value2 WHERE condition;

DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid, but empty table. This query delete all data from "users" table:

postgres=# DELETE FROM users;

In case we want delete all users, who logined earlier than 30 days ago:

postgres=# DELETE FROM users WHERE last_login < NOW() - INTERVAL '30 days';

Exit from psql after all configurations. Command return you in BASH cli:

postgres=# \q

The idea behind this dump method is to generate a file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose. The basic usage of this command is: