How to set autocommit off in postgreSQL ?

 

postgres=# \set AUTOCOMMIT off

postgres=#

postgres=# \echo :AUTOCOMMIT

off

postgres=#

How to check autocommit in PostgreSQL ?

 

postgres=# \echo :AUTOCOMMIT

on

postgres=#

How to check command history in PostgreSQL ?

 

postgres=# \s

\d

\l

\l

\c

\c postgres

select pg_backend_pid();

alter user postgres password 'abcd12345';

\c demo

\c

\l

\c

\l

select * from pg_database;

show data_directory

show data_directory;

show data_directory;

select setting from pg_settings;

select * from pg_settings;

show data_directory;

\q

\conninfo

SELECT *

FROM pg_settings

WHERE name = 'port';

\c

\!

\! ls -lrt

\s

\d

\l

\s

postgres=#

How to execute OS command from psql prompt ?

 

postgres=#

postgres=# \!

bash-4.4$

bash-4.4$

bash-4.4$ ls -la

total 16

drwx------.  3 postgres postgres   79 Jan 15 08:33 .

drwxr-xr-x. 47 root     root     4096 Jan 10 12:04 ..

drwx------.  4 postgres postgres   51 Jan 10 12:05 16

-rw-------.  1 postgres postgres 1405 Jan 15 12:17 .bash_history

-rwx------.  1 postgres postgres  391 Jan 15 08:33 .bash_profile

-rw-------.  1 postgres postgres  352 Jan 15 10:00 .psql_history

bash-4.4$ 

postgres=#

postgres=#

postgres=#

postgres=# \! ls -lrt

total 0

drwx------. 4 postgres postgres 51 Jan 10 12:05 16

postgres=#

How to check database directory in PostgreSQL ?

 

postgres=# show data_directory;

     data_directory

------------------------

 /var/lib/pgsql/16/data

(1 row)

postgres=#

How to check port in postgreSQL?

 

How to check which port running our postgreSQL database ?

[postgres@myserver ~]$ netstat -plunt |grep postgres

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      5963/postgres

tcp6       0      0 ::1:5432                :::*                    LISTEN      5963/postgres

[postgres@myserver ~]$

[postgres@myserver ~]$

postgres=# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "5432".

postgres=#

postgres=#

postgres=#

postgres=# SELECT *

postgres-# FROM pg_settings

postgres-# WHERE name = 'port';

 name | setting | unit |                       category                       |                short_desc

     | extra_desc |  context   | vartype | source  | min_val | max_val | enumvals | boot_val | reset_val | sourcefil

e | sourceline | pending_restart

------+---------+------+------------------------------------------------------+-------------------------------------

-----+------------+------------+---------+---------+---------+---------+----------+----------+-----------+----------

--+------------+-----------------

 port | 5432    |      | Connections and Authentication / Connection Settings | Sets the TCP port the server listens

 on. |            | postmaster | integer | default | 1       | 65535   |          | 5432     | 5432      |

  |            | f

(1 row)

postgres=#

How to connect to PostgreSQL database ?

 

[postgres@myserver ~]$ psql -p 5432 -U postgres -d postgres

Password for user postgres:

psql (16.6)

Type "help" for help.

postgres=# exit

How to check postgres database start time ?

Query to check PostgreSQL database start time  : 

How to check Postgres Schema size using psql query ?

 

Check Postgres Schema size using psql query

Query1 :

SELECT     pg_catalog.pg_namespace.nspname,

           pg_size_pretty(SUM(pg_relation_size(pg_catalog.pg_class.oid))::BIGINT)

FROM       pg_catalog.pg_class

           INNER JOIN pg_catalog.pg_namespace

           ON         relnamespace = pg_catalog.pg_namespace.oid

GROUP BY   pg_catalog.pg_namespace.nspname;

Query 2 : 

with SchemaSize as

(

select ps.nspname as schema_name,

sum(pg_relation_size(pc.oid)) as total_size

from pg_class pc

join pg_catalog.pg_namespace ps

on ps.oid = pc.relnamespace

group by ps.nspname

)

select ss.schema_name,

pg_size_pretty(ss.total_size)

from SchemaSize ss

order by ss.total_size desc

limit 20;

How to grant Permission in PostgreSQL ?

 Grant Permission in PostgreSQL  

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA demo_admin TO demo_rw;

 

GRANT USAGE ON SCHEMA demo_admin TO demo_rw;

 

grant demo_rw to demo_app;

 

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA demo_admin TO demo_rw;

How to grant access to users in PostgreSQL?

Here are some common statement to grant access to a PostgreSQL user:

=> Grant CONNECT to the database:

GRANT CONNECT ON DATABASE database_name TO username;

=> Grant USAGE on schema:

GRANT USAGE ON SCHEMA schema_name TO username;

=> Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;

=> Grant all privileges on all tables in the schema:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;

=> Grant all privileges on all sequences in the schema:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;

=> Grant all privileges on the database:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

=> Grant permission to create database:

ALTER USER username CREATEDB;

=> Make a user superuser:

ALTER USER myuser WITH SUPERUSER;

=> Remove superuser status:

ALTER USER username WITH NOSUPERUSER;

=> Those statements above only affect the current existing tables. To apply to newly created tables, you need to use alter default.

 For example:

ALTER DEFAULT PRIVILEGES FOR USER username IN SCHEMA schema_name

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

How to know Postgresql server uptime ?

 

Login as privileged user to database 

Query :

SELECT pg_postmaster_start_time();

output :

demo1=> SELECT pg_postmaster_start_time();

   pg_postmaster_start_time

-------------------------------

 2023-10-19 08:46:45.432054+00

(1 row)

demo1=>

Postres db Error : connection failed: FATAL: password authentication failed for user -AWS Aurora Postgres/RDS

Postgres Connection error : pgAdmin

connection failed: FATAL: password authentication failed for user "amd" connection to server at "dev-app-instance-1.gbhfthy.us-east-1.rds.amazonaws.com" (10.8767.11.10), port 5432 failed: FATAL: password authentication failed for user "amd"

Solution: 

create database abcd;

create user amd password 'amd';

grant connect on database abcd to rds_superuser;

grant  rds_superuser to amd;

Postgres db connection Error - Status : Failure -Test failed: FATAL: database "db_admin" does not exist

 

Status : Failure -Test failed: FATAL: database "db_admin" does not exist

Workaround: 

while connect using oracle sqlpdeveper to aurora postgres database/postgrs  database and you have a different user and db name. 

username: db_admin

password: password

Host: dev-app.cluster-abcsdedfff1afs.ue-east-1.rds.amazonaws.com/india?

Port: 5432

Solution - workaround

How to connect with PostgreSQL database using oracle SQLDeveloper ?

 

• Check the current sqldeveloper tool version 

• Download reqired postgres driver using below link 

https://jdbc.postgresql.org/download/

• Configure & add driver in the sqldevloper tool as below  

• After setup restart the sqldevloper tool  you will see the postgreSQL in option as below 

• Use postgres db login details and make connection 

You did it

 !!!!! Congarutaions !!!!!

How to Fix psql: error: could not connect to server: No such file or directory in PostgreSQL

 

Error : psql: error: could not connect to server: No such file or directory

-bash-4.2$ psql

psql: error: could not connect to server: No such file or directory

        Is the server running locally and accepting

        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

-bash-4.2$

Check wheather postgres cluster is running or not using below comand 

-bash-4.2$ ps -ef | grep -i postgres

root      3318  3253  0 06:05 pts/0    00:00:00 sudo su - postgres

root      3319  3318  0 06:05 pts/0    00:00:00 su - postgres

postgres  3320  3319  0 06:05 pts/0    00:00:00 -bash

postgres  3494  3320  0 06:22 pts/0    00:00:00 ps -ef

postgres  3495  3320  0 06:22 pts/0    00:00:00 grep --color=auto -i postgres

-bash-4.2$

-bash-4.2$

If postgreSQL cluster is not running then strat using below command 

-bash-4.2$

-bash-4.2$ pg_ctl -D /postgres/data/ start

waiting for server to start....2023-09-01 06:23:16.881 UTC [3508] LOG:  starting PostgreSQL 12.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

2023-09-01 06:23:16.881 UTC [3508] LOG:  listening on IPv4 address "0.0.0.0", port 5432

2023-09-01 06:23:16.881 UTC [3508] LOG:  listening on IPv6 address "::", port 5432

2023-09-01 06:23:16.883 UTC [3508] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

2023-09-01 06:23:16.887 UTC [3508] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

2023-09-01 06:23:16.917 UTC [3508] LOG:  redirecting log output to logging collector process

2023-09-01 06:23:16.917 UTC [3508] HINT:  Future log output will appear in directory "log".

 done

server started

-bash-4.2$

-bash-4.2$

verify using below commnads 

-bash-4.2$

-bash-4.2$

-bash-4.2$ ps -ef | grep -i postgres

root      3318  3253  0 06:05 pts/0    00:00:00 sudo su - postgres

root      3319  3318  0 06:05 pts/0    00:00:00 su - postgres

postgres  3320  3319  0 06:05 pts/0    00:00:00 -bash

postgres  3508     1  0 06:23 ?        00:00:00 /usr/pgsql-12/bin/postgres -D /postgres/data

postgres  3509  3508  0 06:23 ?        00:00:00 postgres: logger

postgres  3511  3508  0 06:23 ?        00:00:00 postgres: checkpointer

postgres  3512  3508  0 06:23 ?        00:00:00 postgres: background writer

postgres  3513  3508  0 06:23 ?        00:00:00 postgres: walwriter

postgres  3514  3508  0 06:23 ?        00:00:00 postgres: autovacuum launcher

postgres  3516  3508  0 06:23 ?        00:00:00 postgres: stats collector

postgres  3517  3508  0 06:23 ?        00:00:00 postgres: logical replication launcher

postgres  3525  3320  0 06:23 pts/0    00:00:00 ps -ef

postgres  3526  3320  0 06:23 pts/0    00:00:00 grep --color=auto -i postgres

-bash-4.2$

-bash-4.2$

-bash-4.2$ psql

psql (12.16)

Type "help" for help.

postgres=# select version();

                                                 version

----------------------------------------------------------------------------------------------------------

 PostgreSQL 12.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

(1 row)

postgres=#

postgres=#