Backup and Restore in postgreSQL

=============================
Copy data only in text format
=============================

hr1dev=# select * from test.emp;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
   101 | John     | User  | 1987-11-12 |     20
(2 rows)


hr1dev=# copy test.emp to '/tmp/emp.txt';
COPY 2

$ cat /tmp/emp.txt
100     Gouranga        Admin   1983-01-10      10
101     John    User    1987-11-12      20

hr1dev=# copy emp to '/tmp/empdata.txt';
COPY 1000100
hr1dev=# delete from emp;
DELETE 1000100
hr1dev=# select count(*) from emp;           
 count 
-------
     0
(1 row)

hr1dev=# copy emp from '/tmp/empdata.txt';
COPY 1000100
hr1dev=# select count(*) from emp;
  count  
---------
 1000100
(1 row)

hr1dev=# 

======================
export with delimiter:
======================

hr1dev=# copy emp to '/tmp/emp_del.txt' with delimiter '|';
COPY 1000100
hr1dev=# 

======================
export with header:
======================
hr1dev=# copy emp to '/tmp/emp_head' with CSV HEADER;
COPY 1000100
hr1dev=# 

$ more /tmp/emp_head
no,name
1,data1
2,data2
3,data3
4,data4

=======================================
export data for specific query:
=======================================
hr1dev=#  copy (select * from emp where no>10000) to '/tmp/emp_query.txt';
COPY 990000



-----------------------------------------------------------
BACKUP AND RESTORE
-----------------------------------------------------------
In postgresql we have 2 levels of backups.

1. Logical backup
2. Physical backup (file system backup/hot backup/online backup/offline backup)

Logical Backup:
---------------
Dump file will be in the format of logical , it will not contains any physical files means configuration files/ data files/transaction logs. To perform logical backup we can use two utilities. These are :
   a. pg_dump
   b, pg_dumpall

a.pg_dump : 
Using pg_dump, following are possible:
1. Single database backup at a time
2. Single schema,multiples schemas backup at a time
3. Single table,multiple tables backup at a time.
4. Dump can be taken in customized format means .tar or .gz directory formats.
7. Parallel jobs can be invoked
8. To restore, we can use traditional restore utility, this 10 times faster than the older utility.

Using pg_dump, we can't perform below activities:
1. we can't take global objects backup
2. Full server logical backup also not possible at a time.

b.pg_dumpall

Using pg_dumpall, following are possible:
1. Complete server backup at a time (all databases backup at a time)
2. Global objects backup only possible

We can't do the following using pg_dumpall utility:
1. Single database/table/schema backup not possible.

2. Taking dump in customized format is not possible.

5. we can't perform parallel jobs
6. here we can't use traditional restore utility.

To restore,  we can use 2 utilities:
-----------------------------------------
1.psql
2.pg_restore (traditional utility)

normally taking dump we have 2 ways:

1.plain format : we can able to read the dump file, what ever the statements you want to execute , you can simply execute as normal sql statements. while restoring you can't use pg_restore utility if dump in plain format.
2. If your dump in plain format , if you dump contains 10 tables backup, you can't restore single table from the dump.
3. To restore manually you have to copy that respective sql statements from the dump.
4. If dump in readable format, there might be number of chances for data misuse. this will be security compliance.
5. Always prefer to take dump in customized for, which is in binary format, not in the readable format. At the same time we can restore single table even-though dump contains multiple tables.
we can use pg_restore utility for restore as well.


Syntax: pg_dump -d db_name -t table_name

-- Taking backup in plain text format

$ /usr/local/pgsql/bin/pg_dump -t test.emp -d hr1dev > emp.sql

-bash-4.2$ pwd
/var/lib/pgsql/backups
-bash-4.2$ cat emp.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.0
-- Dumped by pg_dump version 11.0

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = hr_tbs;
SET default_with_oids = false;
--
-- Name: emp; Type: TABLE; Schema: test; Owner: postgres; Tablespace: hr_tbs
--
CREATE TABLE test.emp (
    empno integer NOT NULL,
    ename character varying NOT NULL,
    job character varying,
    dob date,
    deptno integer
);
ALTER TABLE test.emp OWNER TO postgres;
--
-- Data for Name: emp; Type: TABLE DATA; Schema: test; Owner: postgres
--
COPY test.emp (empno, ename, job, dob, deptno) FROM stdin;
100     Gouranga        Admin   1983-01-10      10
101     John    User    1987-11-12      20
\.
SET default_tablespace = '';
--
-- Name: emp emp_pkey; Type: CONSTRAINT; Schema: test; Owner: postgres
--
ALTER TABLE ONLY test.emp
    ADD CONSTRAINT emp_pkey PRIMARY KEY (empno);

--
-- Name: TABLE emp; Type: ACL; Schema: test; Owner: postgres
--
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE test.emp TO gouranga;
--
-- PostgreSQL database dump complete
--
----------------------------------------------------------------------------------------------------------

=================
Restore from dump
=================

postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# drop table test.emp; 
ERROR:  cannot drop table test.emp because other objects depend on it
DETAIL:  materialized view test.emp_mv depends on table test.emp
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

hr1dev=# drop table test.emp CASCADE;
NOTICE:  drop cascades to materialized view test.emp_mv
DROP TABLE
hr1dev=# 


$ psql -d hr1dev -f emp.sql -o emp_hr1dev_imp.log

-f : filename where data exported in text format
-d : in which database to be imported
-o : output log file

hr1dev=# select * from test.emp;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
   101 | John     | User  | 1987-11-12 |     20
(2 rows)


======================================
Export dump in customized format:
======================================

pg_dump -Fc -d hr1dev -f hr1dev.bin -v 2>dbbackup.log

Taking Dump in parallel format:
--------------------------------------
pg_dump -Fc -d hr1dev -f hr1dev.bin -v -j 2 2>dbbackup.log

Taking backup from remote system:
-----------------------------------------------
pg_dump -Fc -d hr1dev -f hr1dev.bin -v -j 2 -h 192.168.10.132 -p 5433 2>dbbackup.log

for location:
----------------
$ pg_dump -Fd -d hr1dev -f /fullpath/dbbackup -v -j 2 -h 192.168.10.132 -p 5433 2>dbbackup.log

example:
---------
$ pg_dump -Fc -d hr1dev -f hr1dev.bin -v 2>dbbackup.log
$ ll
total 22264
-rw-r--r--. 1 postgres postgres     3255 Dec 29 08:48 dbbackup.log
-rw-r--r--. 1 postgres postgres 22783616 Dec 29 08:48 hr1dev.bin
$ cat dbbackup.log
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "test.sample"
pg_dump: finding the columns and types of table "test.sample2"
pg_dump: finding the columns and types of table "public.emp"
pg_dump: finding the columns and types of table "public.emp_mv"
pg_dump: finding the columns and types of table "test.emp"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "test.emp"
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row security enabled for table "test.sample"
pg_dump: reading policies for table "test.sample"
pg_dump: reading row security enabled for table "test.sample2"
pg_dump: reading policies for table "test.sample2"
pg_dump: reading row security enabled for table "public.emp"
pg_dump: reading policies for table "public.emp"
pg_dump: reading row security enabled for table "public.emp_mv"
pg_dump: reading policies for table "public.emp_mv"
pg_dump: reading row security enabled for table "test.seq1"
pg_dump: reading policies for table "test.seq1"
pg_dump: reading row security enabled for table "test.emp"
pg_dump: reading policies for table "test.emp"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "test.sample"
pg_dump: reading publication membership for table "test.sample2"
pg_dump: reading publication membership for table "public.emp"
pg_dump: reading publication membership for table "test.emp"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: saving database definition
pg_dump: dumping contents of table "public.emp"
pg_dump: dumping contents of table "test.emp"
pg_dump: dumping contents of table "test.sample"
pg_dump: dumping contents of table "test.sample2"
$ 

Restore database from backup:
=============================
pg_restore -Fd -d hr1dev hr1dev.bin -v -j 2 2>db_hr1dev_restore.log


postgres=# drop database hr1dev;
DROP DATABASE
postgres=# \c hr1dev
FATAL:  database "hr1dev" does not exist
Previous connection kept
postgres=# 

postgres=# create database hr1dev;
CREATE DATABASE

$ pg_restore -Fc -d hr1dev hr1dev.bin -v -j 2 2>db_hr1dev_restore.log

$ cat db_hr1dev_restore.log
pg_restore: connecting to database for restore
pg_restore: processing item 3107 ENCODING ENCODING
pg_restore: processing item 3108 STDSTRINGS STDSTRINGS
pg_restore: processing item 3109 SEARCHPATH SEARCHPATH
pg_restore: processing item 3110 DATABASE hr1dev
pg_restore: processing item 6 SCHEMA test
pg_restore: creating SCHEMA "test"
pg_restore: processing item 199 TABLE emp
pg_restore: creating TABLE "public.emp"
pg_restore: processing item 200 MATERIALIZED VIEW emp_mv
pg_restore: creating MATERIALIZED VIEW "public.emp_mv"
pg_restore: processing item 202 TABLE emp
pg_restore: creating TABLE "test.emp"
pg_restore: processing item 197 TABLE sample
pg_restore: creating TABLE "test.sample"
pg_restore: processing item 198 TABLE sample2
pg_restore: creating TABLE "test.sample2"
pg_restore: processing item 201 SEQUENCE seq1
pg_restore: creating SEQUENCE "test.seq1"
pg_restore: entering main parallel loop
pg_restore: launching item 3101 TABLE DATA emp
pg_restore: launching item 3104 TABLE DATA emp
pg_restore: processing data for table "test.emp"
pg_restore: processing data for table "public.emp"
pg_restore: finished item 3104 TABLE DATA emp
pg_restore: launching item 3099 TABLE DATA sample
pg_restore: processing data for table "test.sample"
pg_restore: finished item 3099 TABLE DATA sample
pg_restore: launching item 3100 TABLE DATA sample2
pg_restore: processing data for table "test.sample2"
pg_restore: finished item 3100 TABLE DATA sample2
pg_restore: launching item 3113 SEQUENCE SET seq1
pg_restore: executing SEQUENCE SET seq1
pg_restore: finished item 3113 SEQUENCE SET seq1
pg_restore: launching item 2976 CONSTRAINT emp emp_pkey
pg_restore: creating CONSTRAINT "test.emp emp_pkey"
pg_restore: finished item 2976 CONSTRAINT emp emp_pkey
pg_restore: finished item 3101 TABLE DATA emp
pg_restore: launching item 3111 ACL SCHEMA test
pg_restore: launching item 3112 ACL TABLE emp
pg_restore: creating ACL "test.TABLE emp"
pg_restore: creating ACL "SCHEMA test"
pg_restore: finished item 3112 ACL TABLE emp
pg_restore: finished item 3111 ACL SCHEMA test
pg_restore: launching item 3102 MATERIALIZED VIEW DATA emp_mv
pg_restore: creating MATERIALIZED VIEW DATA "public.emp_mv"
pg_restore: finished item 3102 MATERIALIZED VIEW DATA emp_mv
pg_restore: finished main parallel loop

Testing:
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# \dt test.*
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 test   | emp     | table | postgres
 test   | sample  | table | gouranga
 test   | sample2 | table | postgres
(3 rows)

hr1dev=# 


Example-2:
pg_dump -Fd -d demodb -f dbbackup -v -j 2 2>dbdemo_backup.log

postgres=# drop database demodb;
DROP DATABASE

pg_restore -Fd -d demodb dbbackup -v -j 2 2>db_demodb_restore.log


postgres=# create database demodb;
CREATE DATABASE
postgres=# \q

-bash-4.2$ pg_restore -Fd -d demodb dbbackup -v -j 2 2>db_demodb_restore.log
-bash-4.2$ 
-bash-4.2$ cat db_demodb_restore.log
pg_restore: connecting to database for restore
pg_restore: processing item 3079 ENCODING ENCODING
pg_restore: processing item 3080 STDSTRINGS STDSTRINGS
pg_restore: processing item 3081 SEARCHPATH SEARCHPATH
pg_restore: processing item 3082 DATABASE demodb
pg_restore: processing item 196 TABLE emp
pg_restore: creating TABLE "public.emp"
pg_restore: entering main parallel loop
pg_restore: launching item 3076 TABLE DATA emp
pg_restore: processing data for table "public.emp"
pg_restore: finished item 3076 TABLE DATA emp
pg_restore: finished main parallel loop
-bash-4.2$ psql
psql (11.0)
Type "help" for help.

postgres=# \c demodb
You are now connected to database "demodb" as user "postgres".
demodb=# select count(1) from emp;
 count 
-------
    50
(1 row)

demodb=# 

==============================================
To take complete server level backup:
==============================================
1. parallel not possible
2. only plain format will work.
3. use pg_dumpall

To Take backup:
----------------
$ pg_dumpall>fullserverbackup.sql
$ ls -ltr fullserverbackup.sql
-rw-r--r--. 1 postgres postgres 17786563 Dec 29 09:44 fullserverbackup.sql


To Restore
---------------
$ psql -f fullserverbackup.sql -o fullserverrestore.log

This dump will contains users and user access rights information and table spaces information, this is very rare . Even-though you are taking backup of full server, if required point in-time recovery possible. Only with physical backup only we can able to do point in-time recovery.


PHYSICAL BACKUP:

1. File system backup: after stopping application and jobs related to databases, we will apply check point and we will copy data directory.
2. Hot backup: 
3. offline backup : stop services and by using any Linux command copy data directory to any other location.
4. online backup: no need to stop jobs, no need to stop services, simply you can take the backup by using an utility called pg_basebackup.


pg_basebackup:
===============
It will contain exact copy of our data directory with proper check point. it will gives consistency.
While triggering this one first it will apply check point , and it will start actual backup.
To take this backup you need empty directory always.
For plain database, you can take the full backup within the server, but if you have table-spaces, then you are going to take backup,
simply data directory will be backed up to respective directory. but table-space location will be trying to backed up to existing location
only, if that is empty, backup will success, if that is not empty we have to remap.


Example:

List tablespaces:

postgres=# \db+
                                      List of tablespaces
    Name    |  Owner   |     Location      | Access privileges | Options |  Size  | Description 
------------+----------+-------------------+-------------------+---------+--------+-------------
 hr_tbs     | postgres | /usr/pgsql/hr_tbs |                   |         | 16 kB  | 
 pg_default | postgres |                   |                   |         | 207 MB | 
 pg_global  | postgres |                   |                   |         | 574 kB | 
(3 rows)

postgres=# 


$ pg_basebackup -D /var/lib/pgsql/base_backup/ -X fetch -P -T /usr/pgsql/hr_tbs=/usr/pgsql/hr_tbs_new
229205/229205 kB (100%), 2/2 tablespaces
$ 

You can backup in .tar format also:

$ pg_basebackup -D /var/lib/pgsql/base_backup/ -X fetch -Ft -P -T /usr/pgsql/hr_tbs=/usr/pgsql/hr_tbs_new
229206/229206 kB (100%), 2/2 tablespaces
$ 

Note: Ensure 'base_backup' must be empty.

$ pwd
/var/lib/pgsql
$ cd base_backup/
-bash-4.2$ ls -ltr
total 229212
-rw-------. 1 postgres postgres     19968 Dec 29 10:18 24634.tar
-rw-------. 1 postgres postgres 234689024 Dec 29 10:18 base.tar
$ 

You can see here, all folders backed up:

$ pwd
/var/lib/pgsql/base_backup
$ ls -ltr
total 60
-rw-------. 1 postgres postgres   226 Dec 29 10:14 backup_label
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_dynshmem
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_commit_ts
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_twophase
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_subtrans
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_snapshots
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_serial
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_notify
drwx------. 4 postgres postgres    34 Dec 29 10:14 pg_multixact
drwx------. 7 postgres postgres    62 Dec 29 10:14 base
drwx------. 2 postgres postgres    18 Dec 29 10:14 pg_tblspc
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_replslot
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_stat
-rw-------. 1 postgres postgres 23806 Dec 29 10:14 postgresql.conf
-rw-------. 1 postgres postgres    88 Dec 29 10:14 postgresql.auto.conf
drwx------. 2 postgres postgres    17 Dec 29 10:14 pg_xact
-rw-------. 1 postgres postgres     3 Dec 29 10:14 PG_VERSION
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_stat_tmp
drwx------. 4 postgres postgres    65 Dec 29 10:14 pg_logical
-rw-------. 1 postgres postgres  1636 Dec 29 10:14 pg_ident.conf
-rw-------. 1 postgres postgres  4513 Dec 29 10:14 pg_hba.conf
drwx------. 2 postgres postgres  4096 Dec 29 10:14 pg_log
drwx------. 3 postgres postgres    28 Dec 29 10:14 hr_tbs_new
drwx------. 2 postgres postgres     6 Dec 29 10:14 hr_tbs
-rw-------. 1 postgres postgres    47 Dec 29 10:14 current_logfiles
drwx------. 2 postgres postgres  4096 Dec 29 10:14 global
drwx------. 3 postgres postgres    58 Dec 29 10:14 pg_wal
-bash-4.2$ 

For TAR format no need to mention table-space remapping, it will be create number of files based on number of table-spaces.

$ pwd
/var/lib/pgsql/backup
$ pg_basebackup -D /var/lib/pgsql/backup/ -X fetch -Ft -P
229206/229206 kB (100%), 2/2 tablespaces
$ 
$ ls -ltr
total 229212
-rw-------. 1 postgres postgres     19968 Dec 29 10:23 24634.tar
-rw-------. 1 postgres postgres 234689536 Dec 29 10:23 base.tar
$ pwd
/var/lib/pgsql/backup

================================
Restore from Full Backup
================================

[postgres@example02 backup]$ tar -xvf base.tar
[postgres@example02 backup]$ rm base.tar

[postgres@example02 backup]$ tar -xvf 16613.tar
PG_12_201909212/

[postgres@example02 backup]$ cat tablespace_map
16613 /var/lib/pgsql/hr_tbs_new


update tablespace_map file wit new table space location.
change port in postgresql.conf file of backup directory.

[postgres@example02 backup]$ chmod 0700 /var/lib/pgsql/backup/

[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/backup start
waiting for server to start....    3372 2019-12-24 17:58:55 PST  1LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
    3372 2019-12-24 17:58:55 PST  2LOG:  listening on IPv6 address "::1", port 5433
    3372 2019-12-24 17:58:55 PST  3LOG:  listening on IPv4 address "127.0.0.1", port 5433
    3372 2019-12-24 17:58:55 PST  4LOG:  listening on IPv4 address "192.168.38.132", port 5433
    3372 2019-12-24 17:58:55 PST  5LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
    3372 2019-12-24 17:58:55 PST  6LOG:  redirecting log output to logging collector process
    3372 2019-12-24 17:58:55 PST  7HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@example02 backup]$ ps -ef|grep postgres
root       2815   2784  0 16:47 pts/0    00:00:00 su - postgres
postgres   2816   2815  0 16:47 pts/0    00:00:00 -bash
postgres   2851      1  0 16:47 ?        00:00:00 /usr/local/pgsql/bin/postgres
postgres   2852   2851  0 16:47 ?        00:00:00 postgres: logger
postgres   2854   2851  0 16:47 ?        00:00:00 postgres: checkpointer
postgres   2855   2851  0 16:47 ?        00:00:00 postgres: background writer
postgres   2856   2851  0 16:47 ?        00:00:00 postgres: walwriter
postgres   2857   2851  0 16:47 ?        00:00:00 postgres: stats collector
postgres   2858   2851  0 16:47 ?        00:00:00 postgres: logical replication launcher
postgres   3372      1  0 17:58 ?        00:00:00 /usr/local/pgsql/bin/postgres -D /var/lib/pgsql/backup
postgres   3373   3372  0 17:58 ?        00:00:00 postgres: logger
postgres   3375   3372  0 17:58 ?        00:00:00 postgres: checkpointer
postgres   3376   3372  0 17:58 ?        00:00:00 postgres: background writer
postgres   3377   3372  0 17:58 ?        00:00:00 postgres: walwriter
postgres   3378   3372  0 17:58 ?        00:00:00 postgres: stats collector
postgres   3379   3372  0 17:58 ?        00:00:00 postgres: logical replication launcher
postgres   3380   2816  0 17:59 pts/0    00:00:00 ps -ef
postgres   3381   2816  0 17:59 pts/0    00:00:00 grep --color=auto postgres
[postgres@example02 backup]$


Now 2 instances running in the same server.
with same binaries, different data directories, different ports we can start number of instances.

[postgres@example02 backup]$ psql -p5433
psql (12.0)
Type "help" for help.

postgres=# show data_directory;
    data_directory
-----------------------
 /var/lib/pgsql/backup
(1 row)

postgres=#


[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/DATA/ status
pg_ctl: server is running (PID: 2851)
/usr/local/pgsql/bin/postgres
[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/backup/ status
pg_ctl: server is running (PID: 3372)
/usr/local/pgsql/bin/postgres "-D" "/var/lib/pgsql/backup"
[postgres@example02 backup]$


Anywhere backup strategy will be physical only. daily full backup. 
There is no incremental concept in PostgreSQL.
They will schedule script to create folder before backup with the date.

How you enable basic audits in postgres? What parameters you will use in postgres.conf file

To track each everything happening database level, we need to enable audit log.
To enable this we need to configure below parameter in postgresql.conf file.


log_destination = 'stderr' # audit captured in .txt file. we can write .csv also using 'stderr,crsvlog'
logging_collector = on # entries will be made, who connected and what actions performed. 
log_directory = 'pg_log'    # Log file will be created in pg_log directory inside the data directory
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # format of log files
log_rotation_age = 1d # log file will be rotated every day
log_rotation_size = 10MB  # every log file will be 10MB max
log_min_duration_statement = 0    # this value in milli seconds, 60000 ms for one miniute--for tracking query execution more than mentioned time. -1, disable, 0 - capture all the queries
log_connections = on # What time connected
log_disconnections = on # what time dissconncted
log_line_prefix = '%a %u  %d %h %p %t %i %e' # %a -application, %u -user, %d -database, %h -remote host, %p -process id ,%t -time ,%i %e - sql statement
log_statement = 'all'    # it will capture all sql statements

restrat the postgres services to refrect the changes.

e.g.,

$ /usr/local/pgctl/bin/pg_ctl -D /usr/pgsql restart

Basic Database Management in PostgreSQL / postgres

You can see lab work for basic database management. Copied as is form.

Hierarchy:

database(instance) --> multiple schemas(users) --- objects will be there under schema (oracle)

instance--multiple databases are there---under database mutliple schemas are there (here schema means not user)---under schema multiple objects (postgresql)

if you want to run any query we can do schema_name.object_name.

select * from test.emp;

you can't perform cross join between multiple schemas which are under different databases.
you can perform cross join between two schemas if those are under same database. here database will be treated as separate instance oracle.


database connectivity:
======================
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql
psql (11.0)
Type "help" for help.

postgres=#

Find postgres version:

postgres=# show server_version;
 server_version 
----------------
 11.0
(1 row)



to know the connection details:
--------------------------------
postgres=# \c
You are now connected to database "postgres" as user "postgres".

what are the database:
---------------------
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres


what are the users:
-------------------
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


connect to other database:
-------------------------
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=#

template1=# \c template0
FATAL:  database "template0" is not currently accepting connections
Previous connection kept

you can't able to conenct template0 database, we call this as dumb database, it will not allow any connections.
but while creating database u can use this as template if required.

create user:
-----------
template1=# create user pgscott with PASSWORD 'pgscott';
CREATE ROLE
template1=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 pgscott   |                                                            | {}


alter user:
------------
template1=# alter user pgscott with CREATEDB CREATEROLE REPLICATION CONNECTION LIMIT 10 VALID UNTIL '11-18-2019';
ALTER ROLE
template1=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 pgscott   | Create role, Create DB, Replication                       +| {}
           | 10 connections                                            +|
           | Password valid until 2019-11-18 00:00:00-08                |


\h create user

template1=# alter user pgscott password 'sam';
ALTER ROLE


database creation:
-----------------
template1=# create database testdb;
CREATE DATABASE

template1=# alter DATABASE testdb OWNER to pgscott;
ALTER DATABASE
template1=# \l testdb
                            List of databases
 Name | Owner | Encoding |   Collate   |    Ctype    | Access privileges
------+ ------- +----------+-------------+-------------+-------------------
 testdb | pgscott    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)


schemas under database:
-----------------------
testdb=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres


by default if you create any database public schema will come.

create schema:
--------------
testdb=# create schema test;
CREATE SCHEMA
testdb=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
 test   | postgres


create objects under schema:
----------------------------
if you are not giving scheamname it will goes to public schema.

get the list of tables:
----------------------
testdb=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | emp  | table | postgres
(1 row)

testdb=# alter table emp set SCHEMA test;
ALTER TABLE
testdb=# \dt
Did not find any relations.
testdb=# \dt test.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test   | emp  | table | postgres
(1 row)

list of views:
-------------

testdb=# \dv
Did not find any relations.

list of functions:
------------------

testdb=# \df
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

list of materilized views:
---------------------------
testdb=# \dm
Did not find any relations.
testdb=#


how to connect specific database with specific user:
---------------------------------------------------
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott
psql (11.0)
Type "help" for help.

testdb=> \c
You are now connected to database "testdb" as user "pgscott".
testdb=>


if you are connecting from another client:
-----------------------------------------
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott -h 127.0.0.1
psql (11.0)
Type "help" for help.

testdb=>

OR

[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott  

-d : database name
-U : Username

Create Group and add users to group:
------------------------------------------------------

hr1dev=# 
hr1dev=# create user test1;
CREATE ROLE
hr1dev=# create user test2;
CREATE ROLE
hr1dev=# create user test3;
CREATE ROLE
hr1dev=# create group group1;
CREATE ROLE
hr1dev=# alter group group1 add user test1,test2,test3;
ALTER ROLE
hr1dev=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 gouranga  | Create role, Create DB, Replication                       +| {}
           | 10 connections                                             | 
 group1    | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test1     |                                                            | {group1}
 test2     |                                                            | {group1}
 test3     |                                                            | {group1}


hr1dev=# 

=============================================================
AUDIT LOG ENABLING
=============================================================
to track each everything happening db level, we will enable audit log.

to enable this we need to configure below parameter in postgresql.conf file.

log_destination = 'stderr' # audit captured in .txt file. we can write .csv also using 'stderr,crsvlog'
logging_collector = on # entries will be made, who connected and what actions performed.   
log_directory = 'pg_log'    # Log file will be created in pg_log directory inside the data directory 
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # format of log files
log_rotation_age = 1d # log file will be rotated every day
log_rotation_size = 10MB  # every log file will be 10MB max
log_min_duration_statement = 0    # this value in milli seconds, 60000 ms for one miniute--for tracking query execution more than mentioned time. -1, disable, 0 - capture all the queries
log_connections = on # What time connected
log_disconnections = on # what time dissconncted
log_line_prefix = '%a %u  %d %h %p %t %i %e' # %a -application, %u -user, %d -database, %h -remote host, %p -process id ,%t -time ,%i %e - sql statement
log_statement = 'all'    # it will capture all sql statements

restrat the postgres services to refrect the changes.

e.g.,

$ /usr/local/pgctl/bin/pg_ctl -D /usr/pgsql restart

[postgres@example01 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql restart
waiting for server to shut down.... done
server stopped
waiting for server to start....     15632 2019-08-17 20:39:55 PDT  00000LOG:  listening on IPv6 address "::1", port 5432
     15632 2019-08-17 20:39:55 PDT  00000LOG:  listening on IPv4 address "127.0.0.1", port 5432
     15632 2019-08-17 20:39:55 PDT  XX000LOG:  could not bind IPv4 address "192.168.38.129": Cannot assign requested address
     15632 2019-08-17 20:39:55 PDT  XX000HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
     15632 2019-08-17 20:39:55 PDT  00000LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
     15632 2019-08-17 20:39:55 PDT  00000LOG:  redirecting log output to logging collector process
     15632 2019-08-17 20:39:55 PDT  00000HINT:  Future log output will appear in directory "pg_log".
 done
server started

===========================
vgenerate_series
===========================
[postgres@example01 pgsql]$ cd pg_log
[postgres@example01 pg_log]$ ls -ltr
total 4
-rw------- 1 postgres postgres 198 Aug 17 20:39 postgresql-2019-08-17_203955.log
[postgres@example01 pg_log]$ cat postgresql-2019-08-17_203955.log
     15634 2019-08-17 20:39:55 PDT  00000LOG:  database system was shut down at 2019-08-17 20:39:55 PDT
     15632 2019-08-17 20:39:55 PDT  00000LOG:  database system is ready to accept connections
[postgres@example01 pg_log]$



hr1dev=# create table emp(no integer,name varchar(20));
CREATE TABLE

hr1dev=# insert into emp values(generate_series(1,100),'data'||generate_series(1,100));
INSERT 0 100
hr1dev=# \dt+
                      List of relations
 Schema | Name | Type  |  Owner   |    Size    | Description 
--------+------+-------+----------+------------+-------------
 public | emp  | table | postgres | 8192 bytes | 
(1 row)

hr1dev=# insert into emp values(generate_series(1,1000000),'data'||generate_series(1,1000000));
INSERT 0 1000000
hr1dev=# \dt+
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | emp  | table | postgres | 42 MB | 
(1 row)

hr1dev=# 

=================================
set ECHO_HIDDEN on/off
=================================

hr1dev=# \set ECHO_HIDDEN on
hr1dev=# \dx
********* QUERY **********
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;
**************************

                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

hr1dev=# 
hr1dev=# \set ECHO_HIDDEN OFF
hr1dev=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

hr1dev=# 


===================================================
create group
===================================================
hr1dev=# 
hr1dev=# create user test1;
CREATE ROLE
hr1dev=# create user test2;
CREATE ROLE
hr1dev=# create user test3;
CREATE ROLE
hr1dev=# create group group1;
CREATE ROLE
hr1dev=# alter group group1 add user test1,test2,test3;
ALTER ROLE
hr1dev=# alter user test1 CREATEDB;
ALTER ROLE
hr1dev=# alter user test2 CREATEROLE;
ALTER ROLE
hr1dev=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 gouranga  | Create role, Create DB, Replication                       +| {}
           | 10 connections                                             | 
 group1    | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test1     | Create DB                                                  | {group1}
 test2     | Create role                                                | {group1}
 test3     |                                                            | {group1}

hr1dev=# 

================================================
To check the users having passowrd:
================================================

hr1dev=# alter user test1 password 'password';
ALTER ROLE
hr1dev=# select usename,passwd from pg_shadow where passwd IS NOT NULL;
 usename  |               passwd                
----------+-------------------------------------
 gouranga | md510bf419924319e34e10ca0e87cbc7a8f
 test1    | md565cb1da342495ea6bb0418a6e5718c38
(2 rows)

hr1dev=# 


=========================================
To check hba file entries from db level.
=========================================
postgres=# select * from pg_hba_file_rules;
 line_number | type  |   database    | user_name |  address  |                 netmask                 | auth_method | options | error 
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
          84 | local | {all}         | {all}     |           |                                         | trust       |         | 
          86 | host  | {all}         | {all}     | 127.0.0.1 | 255.255.255.255                         | trust       |         | 
          88 | host  | {all}         | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
          91 | local | {replication} | {all}     |           |                                         | trust       |         | 
          92 | host  | {replication} | {all}     | 127.0.0.1 | 255.255.255.255                         | trust       |         | 
          93 | host  | {replication} | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
(6 rows)




====================================================================
Usefull Views
====================================================================
 pg_catalog | pg_indexes   : list of indexes it will display.
  pg_catalog | pg_stat_all_indexes   : all indexes (user created + system created)
 pg_catalog | pg_stat_sys_indexes : only system created indexes   
 pg_catalog | pg_stat_user_indexes  :only user created indexes     

 pg_catalog | pg_stat_all_tables  :system_user created           
 pg_catalog | pg_stat_sys_tables             :system created 
 pg_catalog | pg_stat_user_tables  :only user created table          
 pg_catalog | pg_tables:system+user created    
====================================================================

-- List all tables/ relations under a database including all schemas

hr1dev=# select schemaname,relname from pg_stat_user_tables;
 schemaname | relname 
------------+---------
 test       | sample2
 test       | sample
 public     | emp


=============================
--- Describe a table
=============================
hr1dev=# create table test.emp(empno int primary key NOT NULL,ename varchar NOT NULL,job varchar,dob DATE,deptno int);
CREATE TABLE

vhr1dev=# \dt+ test.emp;
                      List of relations
 Schema | Name | Type  |  Owner   |    Size    | Description 
--------+------+-------+----------+------------+-------------
 test   | emp  | table | postgres | 8192 bytes | 
(1 row)

hr1dev=# \d+ test.emp
                                          Table "test.emp"
 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
 empno  | integer           |           | not null |         | plain    |              | 
 ename  | character varying |           | not null |         | extended |              | 
 job    | character varying |           |          |         | extended |              | 
 dob    | date              |           |          |         | plain    |              | 
 deptno | integer           |           |          |         | plain    |              | 
Indexes:
    "emp_pkey" PRIMARY KEY, btree (empno)

hr1dev=# 


==========================================
create & refresh materialized view
==========================================
hr1dev=# create materialized view test.emp_mv as select * from test.emp;         
SELECT 0
hr1dev=# select * from test.emp_mv;
 empno | ename | job | dob | deptno 
-------+-------+-----+-----+--------
(0 rows)

-- refresh scenarios

hr1dev=# insert into test.emp values(100,'Gouranga','Admin','10-Jan-1983',10);
INSERT 0 1
hr1dev=# select * from test.emp_mv;
 empno | ename | job | dob | deptno 
-------+-------+-----+-----+--------
(0 rows)

hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=# select * from test.emp_mv;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
(1 row)

hr1dev=# 

hr1dev=# insert into test.emp values(101,'John','User','10-Jan-1983',20);
INSERT 0 1
hr1dev=# select * from test.emp_mv;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
(1 row)

hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=#  select * from test.emp_mv;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
   101 | John     | User  | 1983-01-10 |     20
(2 rows)

hr1dev=# 
hr1dev=# update test.emp set dob='12-Nov-1987' where empno=101;
UPDATE 1
hr1dev=# select * from test.emp_mv;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
   101 | John     | User  | 1983-01-10 |     20
(2 rows)

hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=# select * from test.emp_mv;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
   101 | John     | User  | 1987-11-12 |     20
(2 rows)

hr1dev=# 


hr1dev=# select * from pg_matviews;
 schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated |     definition     
------------+-------------+--------------+------------+------------+-------------+--------------------
 public     | emp_mv      | postgres     |            | f          | t           |  SELECT emp.no,   +
            |             |              |            |            |             |     emp.name      +
            |             |              |            |            |             |    FROM emp;
 test       | emp_mv      | postgres     |            | f          | t           |  SELECT emp.empno,+
            |             |              |            |            |             |     emp.ename,    +
            |             |              |            |            |             |     emp.job,      +
            |             |              |            |            |             |     emp.dob,      +
            |             |              |            |            |             |     emp.deptno    +
            |             |              |            |            |             |    FROM test.emp;
(2 rows)



==============================================
sequences
==============================================

hr1dev=# create SEQUENCE test.seq1 AS int INCREMENT 1 MINVALUE 1 MAXVALUE 100;
CREATE SEQUENCE
hr1dev=# select * from pg_sequences;
 schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value 
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
 test       | seq1         | postgres      | integer   |           1 |         1 |       100 |            1 | f     |          1 |           
(1 row)

hr1dev=# \ds
Did not find any relations.
hr1dev=# \ds test.*
          List of relations
 Schema | Name |   Type   |  Owner   
--------+------+----------+----------
 test   | seq1 | sequence | postgres
(1 row)

hr1dev=# select nextval('test.seq1');
 nextval 
---------
       1
(1 row)

hr1dev=# select nextval('test.seq1');
 nextval 
---------
       2
(1 row)

hr1dev=# select * from pg_sequences;
 schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value 
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
 test       | seq1         | postgres      | integer   |           1 |         1 |       100 |            1 | f     |          1 |          2
(1 row)

hr1dev=# 


==============================================================
pg_stat_activity  :currently running sessions from db level.
==============================================================

hr1dev=# select datname,pid,usename,client_addr,backend_start,state,query from pg_stat_activity;
 datname |  pid  | usename  | client_addr |          backend_start           | state  |                                          query                                          
---------+-------+----------+-------------+----------------------------------+--------+-----------------------------------------------------------------------------------------
         |  4132 |          |             | 2019-12-28 22:41:37.543702+05:30 |        | 
         |  4134 | postgres |             | 2019-12-28 22:41:37.544265+05:30 |        | 
 hr1dev  | 10739 | postgres |             | 2019-12-29 00:08:33.238153+05:30 | active | select datname,pid,usename,client_addr,backend_start,state,query from pg_stat_activity;
         |  4130 |          |             | 2019-12-28 22:41:37.53434+05:30  |        | 
         |  4129 |          |             | 2019-12-28 22:41:37.533874+05:30 |        | 
         |  4131 |          |             | 2019-12-28 22:41:37.53488+05:30  |        | 
(6 rows)

hr1dev=# 


postgres=# create database demodb;
CREATE DATABASE
postgres=# select datname,numbackends,temp_files,deadlocks from pg_stat_database;
  datname  | numbackends | temp_files | deadlocks 
-----------+-------------+------------+-----------
 postgres  |           1 |          0 |         0
 template1 |           0 |          0 |         0
 template0 |           0 |          0 |         0
 hr1dev    |           0 |          0 |         0
 demodb    |           0 |          0 |         0
(5 rows)

postgres=# 


===============================================
Grants
===============================================
postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
 test   | gouranga |                      | 
(2 rows)

hr1dev=# 


hr1dev=# grant usage on schema test to gouranga; # gouranga is a user
GRANT
hr1dev=#  grant usage on schema test to group1; # group1 is group of many users
GRANT
hr1dev=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
 test   | gouranga | gouranga=UC/gouranga+| 
        |          | group1=U/gouranga    | 
(2 rows)

hr1dev=# 

if user gouranga is there, he has set of access rights, now at a time one request came to create 10 users with same access rights as 'gouranga' user.

hr1dev=# create user user4 password 'password';
CREATE ROLE

hr1dev=# grant gouranga to user4;
GRANT ROLE
hr1dev=# \du+
                                           List of roles
 Role name |                         Attributes                         | Member of  | Description 
-----------+------------------------------------------------------------+------------+-------------
 gouranga  | Create role, Create DB, Replication                       +| {}         | 
           | 10 connections                                             |            | 
 group1    | Cannot login                                               | {}         | 
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}         | 
 test1     | Create DB                                                  | {group1}   | 
 test2     | Create role                                                | {group1}   | 
 test3     |                                                            | {group1}   | 
 user4     |                                                            | {gouranga} | 

hr1dev=# 

Note: dba_sys_privs in oracle and special privilges in postgresql both are same.

administrable_role_authorizations   : this one will displays users and its assigned roles, but if you ( > postgres 12)

hr1dev=# grant gouranga to user4 with admin option;
GRANT ROLE


===========================================
table_constraints   : all constraints
============================================
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# select constraint_schema,constraint_name,table_name,constraint_type from information_schema.table_constraints where constraint_schema<>'pg_catalog';
 constraint_schema |    constraint_name     | table_name | constraint_type 
-------------------+------------------------+------------+-----------------
 test              | emp_pkey               | emp        | PRIMARY KEY
 test              | 16386_24592_1_not_null | emp        | CHECK
 test              | 16386_24592_2_not_null | emp        | CHECK
(3 rows)

hr1dev=# 



---------------------------
information_schema
----------------------------
 information_schema | table_privileges                      
 information_schema | tables  :list of tables               
 information_schema | triggered_update_columns: if you create any trigger which column will update as part of the trigger.              
 information_schema | triggers    :list of triggers                               
 information_schema | view_column_usage :columns part of view                    
 information_schema | view_table_usage   :tables part of views   



===================================
Database size , Table size etc
===================================
hr1dev=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 demodb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7561 kB | pg_default | 
 hr1dev    | gouranga | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 92 MB   | pg_default | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7699 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7561 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7561 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            | 
(5 rows)

hr1dev=# 

hr1dev-# \dt+ test.*
                       List of relations
 Schema |  Name   | Type  |  Owner   |    Size    | Description 
--------+---------+-------+----------+------------+-------------
 test   | emp     | table | postgres | 16 kB      | 
 test   | sample  | table | gouranga | 8192 bytes | 
 test   | sample2 | table | postgres | 0 bytes    | 
(3 rows)

hr1dev-# 

-- using query

hr1dev=# select pg_size_pretty(pg_relation_size('test.emp'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

hr1dev=# select pg_size_pretty(pg_relation_size('emp'));
 pg_size_pretty 
----------------
 42 MB
(1 row)

hr1dev=# select pg_size_pretty(pg_database_size('hr1dev'));
 pg_size_pretty 
----------------
 92 MB
(1 row)


------------------------------------------------
 Using   TABLESPACES
---------------------------------------------------

TABLESPACE : it is a logical representation for storage space(specific mount point ) or device to store the data. 

             in postgresql by default we have 2 Tablespaces.

postgres=# select * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 


postgres=# show data_directory;                    
 data_directory 
----------------
 /usr/pgsql
(1 row)


postgres=# create tablespace hr_tbs location '/usr/pgsql/hr_tbs';
WARNING:  tablespace location should not be inside the data directory
CREATE TABLESPACE
postgres=# \db+
                                       List of tablespaces
    Name    |  Owner   |     Location      | Access privileges | Options |  Size   | Description 
------------+----------+-------------------+-------------------+---------+---------+-------------
 hr_tbs     | postgres | /usr/pgsql/hr_tbs |                   |         | 0 bytes | 
 pg_default | postgres |                   |                   |         | 122 MB  | 
 pg_global  | postgres |                   |                   |         | 574 kB  | 
(3 rows)

postgres=# 


by default if you create any tablesapce, size will be 0 bytes only, no objects will be created under the specific tablesapce,

so we have to assign tablesapce to specific object, then only objects will be create.

created directory after creating tablespace.


$ pwd
/usr/pgsql/hr_tbs/PG_11_201809051


hr1dev=# select pg_relation_filepath('test.emp');
 pg_relation_filepath 
----------------------
 base/16384/24592

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 demodb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7561 kB | pg_default | 

postgres=# alter database demodb set tablespace hr_tbs;
ALTER DATABASE
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 demodb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7561 kB | hr_tbs     | 

limitations of tablesapces:
---------------------------
one tablesapce can have only one path.
one tablesapce can be assigned to multiple databases/objects.
we can't assign multiple tablesapces to one object.
eventhought your database assigned to different tablespace, you can assign another tablesapce to one of the table under same database.

hr1dev=# select pg_relation_filepath('test.emp');
 pg_relation_filepath 
----------------------
 base/16384/24592


hr1dev=# alter table test.emp set TABLESPACE hr_tbs;
ALTER TABLE
hr1dev=# select pg_relation_filepath('test.emp');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/24634/PG_11_201809051/16384/24635
(1 row)

hr1dev=# 


===================================================
Get DDL of a function
==================================================
-- find oid

hr1dev=# select proname,oid from pg_proc where proname like '%filepath%';
       proname        | oid  
----------------------+------
 pg_relation_filepath | 3034
(1 row)

-- get ddl from oid

hr1dev=# select pg_get_functiondef(3034);
                          pg_get_functiondef                          
----------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION pg_catalog.pg_relation_filepath(regclass)+
  RETURNS text                                                       +
  LANGUAGE internal                                                  +
  STABLE PARALLEL SAFE STRICT                                        +
 AS $function$pg_relation_filepath$function$                         +

(1 row)

hr1dev=#