How to install postgresql in Docker

-

# mkdir /postgres_data_dir

# chmod 777 /postgres_data_dir

# docker run --name postgresql -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=Password@54321 -p 5432:5432 -v /postgres_data_dir:/var/lib/postgresql/data -d postgres

# docker ps

CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                             NAMES

c48395e40dc9   postgres    "docker-entrypoint.s…"   24 minutes ago   Up 24 minutes   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp         postgresql


Install postgresql client in Base Machine:- 

# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# yum install -y postgresql15

# psql -V

psql (PostgreSQL) 15.2


Login in Database:-

# psql -h  localhost -U postgres

Password for user postgres: xxxxxxxxxxxxxxxx

psql (15.2)

Type "help" for help.

postgres=#


CREATE Database:-

postgres=# CREATE database testDB;

CREATE DATABASE


Use Database:-

postgres=# \c testdb ;

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

testdb=# 


How to Check Table:-

test_db=# \d+

                                                      List of relations
 Schema |                 Name                 |   Type   |  Owner   | Persistence | Access method |    Size    | Description 
--------+--------------------------------------+----------+----------+-------------+---------------+------------+-------------
 public | raju_logs                            | table    | postgres | permanent   | heap          | 96 MB      | 
 public | raju_logs_id_seq                     | sequence | postgres | permanent   |               | 8192 bytes | 


How to Check Table With Permission:- 

otr_db=# \dp+ docmt.*

                                       Access privileges

 Schema |       Name       | Type  |      Access privileges      | Column privileges | Policies 

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

 docmt  | raju_fa_photo | table |                             |                   | 

 docmt  | raju_photo    | table | postgres=arwdDxt/postgres  +|                   | 

        |                  |       | query=r/postgres        +|                   | 

        |                  |       | write_username=arw/postgres |                   | 


CREATE Table:- 

testdb=# CREATE TABLE testTABLE (

ID VARCHAR(50),

Email VARCHAR(320),

First_Name VARCHAR(150),

Last_Name VARCHAR(150),

Phone_No numeric(10),

PRIMARY KEY(ID)

);

CREATE TABLE


Show Table:-

testdb=# \d

           List of relations

 Schema |   Name    | Type  |  Owner   

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

 public | testtable | table | postgres

(1 row)


Describe Table:-

testdb=# \d+ testtable

                                                  Table "public.testtable"

   Column   |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 

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

 id         | character varying(50)  |           | not null |         | extended |             |              | 

 email      | character varying(320) |           |          |         | extended |             |              | 

 first_name | character varying(150) |           |          |         | extended |             |              | 

 last_name  | character varying(150) |           |          |         | extended |             |              | 

 phone_no   | numeric(10,0)          |           |          |         | main     |             |              | 

Indexes:

    "testtable_pkey" PRIMARY KEY, btree (id)

Access method: heap


Add Column:-

testdb=# alter table testtable add column City character varying(20);

ALTER TABLE


Increasing the size of character varying type:-

testdb=# alter table testtable alter column City TYPE character varying(10);

ALTER TABLE


Rename Column Name:-

testdb=# alter table testtable rename column city TO address ;

ALTER TABLE


Update existing records in a table:-

testdb=# UPDATE your_table SET column1 = new_value1 WHERE condition;


Delete records from a table:-

testdb=# DELETE FROM your_table WHERE condition;


How to Delete COLUMN:-

testdb=# ALTER TABLE taable_name  DROP COLUMN COLUMN_name;


Create an index on one or more columns to improve query performance:-

testdb=# CREATE INDEX index_name ON your_table (column1, column2);


How to Check Users:-

testdb=# \du+

testdb=# select * from pg_catalog.pg_user;

     usename      | usesysid  | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 

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

 postgres         |        10 | t           | t        | t       | t            | ******** |          | 

 db_treasury_user |     16385 | f           | f        | f       | f            | ******** |          | 


Remove Column:-

testdb=# alter table testtable drop COLUMN address ;

ALTER TABLE


Insert Data in Table:-

testdb=# INSERT INTO testtable VALUES ('1','raju@gmail','raju','kumar','9891800000');

INSERT 0 1


Check Data in Table:-

testdb=# select * from testtable;

 id |   email    | first_name | last_name |  phone_no  

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

 1  | raju@gmail | raju       | kumar     | 9891800000

(1 row)


Update first_name raju to rajeev:-

testdb=# update testtable SET first_name='rajeev' where id='1';

UPDATE 1


Remove PRIMARY KEY:-

testdb=# alter table testtable drop CONSTRAINT testtable_pkey ;

ALTER TABLE


Add PRIMARY KEY:-

testdb=# ALTER TABLE testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);

ALTER TABLE


Remove Data in Table:-

testdb=# truncate table testtable;

TRUNCATE TABLE


Insert Data from CSV:-

# cat  user.csv

id|email|first_name|last_name|phone_no

1|raju@gmail.com|raju|kumar|9800000000

2|rajeev@gmail.com|rajeev|jha|0987654321

3|abc@yahoo.com|abc|mishra|1234567890

4|xyz@ms.in|xyz|sharma|9087123764

5|ram@yahoo.in|ram|babu|9891871234


testdb=# \copy testtable from '/root/user.csv' delimiter '|' CSV HEADER;

COPY 5


Table Data Export to CSV:-

testdb=# \copy testtable to '/tmp/user.csv' WITH DELIMITER ',' CSV HEADER;

COPY 5

# cat  /tmp/user.csv 

id,email,first_name,last_name,phone_no

1,raju@gmail.com,raju,kumar,9800000000

2,rajeev@gmail.com,rajeev,jha,987654321

3,abc@yahoo.com,abc,mishra,1234567890

4,xyz@ms.in,xyz,sharma,9087123764

5,ram@yahoo.in,ram,babu,9891871234


How to Create User:-

test_db=# CREATE USER query  WITH PASSWORD 'Password@123';

CREATE ROLE


How to Provide Access schema:-

test_db=# grant all on ALL tables in schema public to USER_NAME ;


How to Provide Access Table:-

test_db=# grant SELECT,INSERT,UPDATE on Table_Nme  to  User_Name;


Revoke Permission from a User:-

test_db=# revoke All on  Table_Nme  from User_Name;


How to Provide Access to Single Table:-

test_db=# grant all on employees to query ;

GRANT

How to check sequence:- 
test_db=# \ds
                                            List of relations
 Schema  |                              Name                               |   Type   |      Owner       
---------+-----------------------------------------------------------------+----------+------------------
 defprod | data_log_id_seq                                                 | sequence | def_write_user
 defprod | data1_log_id_seq                                                | sequence | postgres
 defprod | add_institutedata_log_id_seq                                    | sequence | def_write_user
 defprod | add_scheme_data_log_id_seq                                      | sequence | def_write_user


How to check sequence permission:-
test_db=# \dp+ data_log_id_seq
                                                       Access privileges
 Schema  |                  Name                  |   Type   |         Access privileges         | Column privileges | Policies 
---------+----------------------------------------+----------+-----------------------------------+-------------------+----------
 defprod | data_log_id_seq                        | sequence | query=rwU/def_write_user+         |                   | 
         |                                        |          | user_name=rwU/def_write_user      |                   | 
(1 row)


How to Provide Access to SEQUENCE:- 
test_db=# grant ALL ON SEQUENCE data_log_id_seq to User_Name ; 


How to Create schema:-

testdb=#  create schema schema_Name;


How to set Default schema:-

testdb=#  alter database DB_NAME  set search_path ='schema_Name';


How to Create extension:-

testdb=# create extension extension_NAME ;


Check the active connections:-

testdb=# SELECT * FROM pg_stat_activity;


How to check PostgreSQL Version:-

testdb=# SELECT version();


Remove Table:-

testdb=# drop table testtable ;

DROP TABLE


Remove Database:-

postgres=# drop database testdb;

DROP DATABASE


Check Current schema:-

test_db=# show search_path;

 search_path 

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

rajuprod

(1 row)


How to check all materialized view:-

test_db=# \dm



How to Check if a materialized view has been refreshed or not in PostgreSQL:-

test_db=# \x
Expanded display is on.
test_db=# SELECT * FROM pg_stat_all_tables where relname='mv_name_applicants_list';
-[ RECORD 1 ]-------+----------------------------------------
relid               | 97539554
schemaname          | public
relname             | mv_name_applicants_list
seq_scan            | 0
seq_tup_read        | 0
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 32806938
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 886756
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 2023-10-27 06:03:43.395302+05:30
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 37


How to copy a table with data from an existing table

test_db= create table test_new_table  as select * from test_old_table ;

DB Dump for Slave:-

/usr/local/pgsql/bin/pg_basebackup -h MASTER_SERVER_IP -U USER_NAME -p 5432 -D /cluster/pg14/data -Fp -Xs -P -R 


Take table dump

# /usr/pgsql-12/bin/pg_dump  -h MASTER_SERVER_IP -p 5432 -d DB_NAME -t mst > /tmp/table_Backup/mst.sql


Take Full DB Dump

# /usr/pgsql-12/bin/pg_dump  -h MASTER_SERVER_IP  -p 5432 -d DB_Name  > /tmp/DB_NAME.sql


Restart Table Dump:-

# /usr/pgsql-12/bin/psql  -h SERVER_IP  -p 5432 -d DB_Name <  /tmp/DB_NAME_Table.sql


Restore Full DB Dump:-

# /usr/pgsql-12/bin/psql  -h SERVER_IP  -p 5432  <  /tmp/DB_NAME.sql




Comments

Post a Comment

Popular posts from this blog

PCS Corosync Pacemaker Cluster Mariadb using NFS

-
              PCS Corosync Pacemaker Cluster Mariadb using NFS I have use 2 node cluster with nfsserver  (NODE1,NODE2 and nfsserver )  OS RHEL7.6 Minimal Install IP 192.168.122.50 node1.example.com node1 IP 192.168.122.51 node2.example.com node2 IP 192.168.122.49 nfsserver.example.com nfsserver [root@node1 ~]# vi /etc/hosts  ( edit in NODE1,NODE2 and nfsserver ) 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.122.49 nfsserver.example.com nfsserver 192.168.122.50 node1.example.com node1 192.168.122.51 node2.example.com node2 #####  Stop Firewall in NODE1,NODE2 and nfsserver  ####### [root@node1 ~]# iptables -F [root@node1 ~]# iptables -F -t nat [root@node1 ~]# systemctl stop firewalld [root@node1 ~]# systemctl disable firewalld [root@node1 ~]# systemctl stop NetworkManager [root@node1 ~]# systemctl disable NetworkManager [root@node1 ~]# vi /etc/sy
Read more

How to install and configure node js and PM2 in rhel7

-
OS RHEL7.6 Minimal Install Download :  libuv-1.7.5-3.el7.x86_64.rpm , nodejs-4.7.2-1.el7.x86_64.rpm [root@node1 ~]# rpm -ivh nodejs-4.7.2-1.el7.x86_64.rpm warning: nodejs-4.7.2-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY error: Failed dependencies: libuv >= 1:1.7.5 is needed by nodejs-4.7.2-1.el7.x86_64 libuv.so.1()(64bit) is needed by nodejs-4.7.2-1.el7.x86_64 [root@node1 ~]# rpm -ivh libuv-1.7.5-3.el7.x86_64.rpm warning: libuv-1.7.5-3.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY Preparing...                          ################################# [100%] Updating / installing...    1:libuv-1:1.7.5-3.el7              ################################# [100%] [root@node1 ~]# rpm -ivh nodejs-4.7.2-1.el7.x86_64.rpm warning: nodejs-4.7.2-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY Preparing...                          ################################# [100%] Updating / installing.
Read more

How to Create or Configure iSCSI Server and Clinet

-
Server: server.example.com IP Address: 192.168.122.246 OS: CentOS 7.9 Client: client.example.com IP Address: 192.168.122.245 Server: [root@server ~]# pvcreate /dev/vdb [root@server ~]# vgcreate newvg /dev/vdb [root@server ~]#  lvcreate -L 10G -n iscsi newvg [root@server ~]# yum install targetcli -y [root@server ~]# targetcli > cd /backstores/block /backstores/block> create iscsi_disk1 /dev/newvg/iscsi Created block storage object iscsi_disk1 using /dev/newvg/iscsi. /backstores/block> cd /iscsi /iscsi> create iqn.2016-02.local.cass2.server:disk1 Created target iqn.2016-02.local.cass2.server:disk1. Created TPG 1. Global pref auto_add_default_portal=true Created default portal listening on all IPs (0.0.0.0), port 3260. /iscsi> cd /iscsi/iqn.2016-02.local.cass2.server:disk1/tpg1/acls  /iscsi/iqn.20...sk1/tpg1/acls> create iqn.2016-02.local.cass2.server:cass1 Created Node ACL for iqn.2016-02.local.cass2.server:cass1 /iscsi/iqn.20...sk1/tpg1/acls> cd /iscsi/iqn.2016-02.
Read more