How to switch/connect with other database (demo)?

 

postgres=#

postgres=#

postgres=#

postgres=# \c demo

You are now connected to database "demo" as user "postgres".

demo=#

demo=#

demo=# \l demo

 demo | postgres | UTF8     | libc            | English_India.1252 | English_India.1252 |        |           |

How to create database in PostgreSQL ?

 

postgres=#

postgres=#

postgres=# create database demo;

CREATE DATABASE

postgres=#

postgres=# \l

 demo      | postgres | UTF8     | libc            | English_India.1252 | English_India.1252 |        |           |

 postgres  | postgres | UTF8     | libc            | English_India.1252 | English_India.1252 |        |           |

 template0 | postgres | UTF8     | libc            | English_India.1252 | English_India.1252 |        |           | =c/postgres          +

           |          |          |                 |                    |                    |        |           | postgres=CTc/postgres

 template1 | postgres | UTF8     | libc            | English_India.1252 | English_India.1252 |        |           | =c/postgres          +

           |          |          |                 |                    |                    |        |           | postgres=CTc/postgres

postgres=#

PostgreSQL : 'more' is not recognized as an internal or external command, operable program or batch file.

 postgres=#

postgres=#

postgres=#

postgres=# \l

'more' is not recognized as an internal or external command,

operable program or batch file.

postgres=#

postgres=#

postgres=# \l

'more' is not recognized as an internal or external command,

operable program or batch file.

postgres=#

This happens in psql on Windows when the MORE command is missing or overridden in the system PATH.

psql uses the system pager (more) to display long output like \l, \dt, etc.

postgres=#

postgres=# \pset pager off

Pager usage is off.

postgres=#

postgres=# \l

                                                            List of databases

   Name    |  Owner   | Encoding | Locale Provider |      Collate       |       Ctype        | Locale | ICU Rules |   Access privileges

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

 postgres  | postgres | UTF8     | libc            | English_India.1252 | English_India.1252 |        |           |

 template0 | postgres | UTF8     | libc            | English_India.1252 | English_India.1252 |        |           | =c/postgres          +

           |          |          |                 |                    |                    |        |           | postgres=CTc/postgres

 template1 | postgres | UTF8     | libc            | English_India.1252 | English_India.1252 |        |           | =c/postgres          +

           |          |          |                 |                    |                    |        |           | postgres=CTc/postgres

(3 rows)

postgres=#

postgres=#

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=#