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