PostgreSQL Tutorials and Solutions

In this article, we have shown PostgreSQL Cheat Sheet with examples.

1. Connection
Connect to PostgreSQL Server using psql, pgadmin and dbeaver
sudo -u postgres psql
\conninfo To get connection information
show server_version;
select version();
cat PG_VERSION
pg_config –version

\! clear –>Clear the screen
\dx to list all extensions installed
\! or \q TO exit
2. Databases
\l To list databases
select datname from pg_database;
\c \connect to the databases
select current_database();
create database
rename database
find database size
select pg_database_size(sales);
All databases size limits in descending order.
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;
drop database
3 Schemas:
Get list of schemas in a database
\dn
SELECT schema_name FROM information_schema.schemata;
How to change current schema?
set search_path to accounts;
To cross check run select current_schema;
create schema
drop schema
4 Tables:
\dt
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
Create table
add column to table
delete column from table
ALTER TABLE IF EXISTS
ADD [];
insert data into tablerename table
find table size:
select pg_relation_size(‘accounts’);
find table path
SELECT pg_relation_filepath(‘table_name’);
delete table
drop table

5 Users:
\du
SELECT rolname FROM pg_roles;
select current_user;
create user
change password
drop user
grant all privileges to users in a database
Grant connection permissions on database
GRANT CONNECT ON DATABASE TO ;
Grant permissions on schema
GRANT USAGE ON SCHEMA public TO ;
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO ;
GRANT SELECT, UPDATE, INSERT ON TO ;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ;
To see what users are running?
select datname,usename, state, query from pg_stat_activity;

6 Miscellaneous commands in psql
How to execute previous command in PostgreSQL?
\g
How to find commnad history in PostgreSQL?
\s
How to edit query in PostgreSQL?
\e
How to execute script from psql?
\i query statement
To save query output from psql shell
\o filename_to_store the output.

PostgreSQL configuration file location:
show config_file;
PostgreSQL hba file location:
show hba_file;

How to check the status of PostgreSQL server:
sudo systemctl status postgresql

Current configuration values
SHOW all;

List of commands/steps involved in Migrate on-premises PostgreSQL databases to Azure PostgreSQL using DMS are :

Setup Azure database for PostgreSQL using below link:

Link/guide to migrate databases from on-prem to Azure cloud using DMS, go through below link:
https://learn.microsoft.com/en-us/azure/dms/tutorial-postgresql-azure-postgresql-online-portal

Setttings in on-prem server:
Settings in postgresql.conf
listen_addresses = '*'
wal_level = logical
max_replication_slots = 5
max_wal_senders = 10

Then to effect these changes, we must restart postgresql server
sudo systemctl restart postgresql

Create following role on on-prem PostgreSQL Server:

CREATE ROLE db_migrationuser WITH
LOGIN
SUPERUSER
CREATEDB
CREATEROLE
INHERIT
REPLICATION
CONNECTION LIMIT -1
PASSWORD 'admin@123';

Grant postgres access to rreplication role:

grant postgres to db_migrationuser;

Add user in pg_hba file

host replication db_migrationuser 0.0.0.0/0 md5

After modification of pg_hba.conf file, we have to connect to postgresql and run below command:

select pg_reload_conf();

pg_dump -O -h ub2022pg12.eastus.cloudapp.azure.com -U postgres -d r2schools -s > r2schools_schema.sql

psql -h az14pgserver.postgres.database.azure.com -U postgres -d r2schools   < r2schools_schema.sql

In this article, we will see solution for the error: pgadmin Utility file not found. Please correct the Binary Path in the Preferences dialog.

Solution for pgadmin Utility file not found. Please correct the Binary Path in the Preferences dialog:

Continue reading pgadmin Utility file not found. Please correct the Binary Path in the Preferences dialog

In this article, we will discuss key differences between Relational and NoSQL databases.

An SQL, or relational database, is excellent for data processing—creating granular connections between pieces of data. A NoSQL database is great for finding one piece of data quickly and operating on it. There’s little to no searching; it just gives you the user data.

Continue reading Difference between relational and NoSQL databases