PostgreSQL - useful commands

03.04.2022 | 703 | 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)