PostgreSQL - useful commands

03.04.2022 | 873 | SQL

Top useful commands for working with PostgreSQL

Installing PostgreSQL DBMS on Ubuntu (relevant for 20.04):

sudo apt install postgresql

Create a database:

create database db_name;

Create a DB user:

create user user_name with encrypted password 'user_password';

Give the user rights to the database:

grant all privileges on database db_name to user_name;

Changing the user's password:

ALTER USER db_name WITH PASSWORD 'user_password';

Go to the database:

c db_name;

Create a table with a structure:

CREATE TABLE db_name ( id integer PRIMARY KEY, lang char(2) NOT NULL, active boolean NOT NULL, active_from timestamp NOT NULL, sort int NOT NULL, name varchar(255) NOT NULL, code varchar(255) NOT NULL, preview_text text, preview_picture varchar(255) DEFAULT NULL, detail_text text NOT NULL, detail_picture varchar(255) DEFAULT NULL, show_counter int NOT NULL DEFAULT '0', created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL );

Show the database structure:

d table_name;

Adding an entry:

INSERT INTO db_name (id, name, description) VALUES(1, 'test', 'test');

Output a list of tables:

dt;

Displaying a list of databases:

l+

Make a text dump:

pg_dump -d db_name db_name.sql

Loading a database from a running MySQL DBMS to PostgreSQL:

pgloader mysql://user_name:user_password@localhost/db_name postgresql://user_name:user_password@localhost/db_name 

Creating a binary dump:

sudo -u postgres pg_dump -Fc db_name > db_name.dump

Download the database from a binary dump with the creation of:

sudo -u postgres pg_restore -C -d postgres db_name.dump

Delete a database:

DROP DATABASE db_name;

Installing a graphical admin panel for a DBMS (under Ubuntu):

wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb dpkg -i dbeaver-ce_latest_amd64.deb rm dbeaver-ce_latest_amd64.deb

← Back

Comments (0)