**************************************************************************************************************************************************
NOTE : By default if You connect any DB in the Cluster with out specified User Name, Eventhough those DBs are owned by different users,
then internally postgres will be the User and Public is the default Schema Name respective to that.
If you create any tables after logging into DB, It will use Public Schema only since this will be the default search path.
If you connect db along with particular user then, objects will be created to the respective corresponding schema pointed to the owner.
Even you revoke Public Schema access to respective of all users or specified users, By default search path will be public only. But since public
is revoked, it will look out who is the owner of the schema to own the object as owner.
ALTER ROLE advuser IN DATABASE "Adventureworks"
SET search_path TO adv_schema;
**************************************************************************************************************************************************
postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
date_part | date_part
-----------+-----------
-4 | 0
postgres=# select now();
now
-------------------------------
2011-06-17 12:54:39.195291-04
https://www.technobytz.com/most-useful-postgresql-commands.html
1. To list out all the table from all the schemas of the current DB
SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','pg_catalog');
2. To list out all the table from particular schemas
SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') AND table_schema IN('public', '<<your_schema_name>>');
3. Getting the size of Database
SELECT pg_database_size(current_database()); or SELECT pg_database_size('<<db_name>>');
4. Getting the size of Database in human redable format
select pg_size_pretty(pg_database_size(current_database()));
or
select pg_size_pretty(pg_database_size('<<db_name>>'));
5. To find size of the table.
select pg_relation_size('accounts');
SELECT pg_size_pretty( pg_total_relation_size('tablename') );
6. Find duplicate rows of the table.
SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id)
note : if you want to delete duplicate then
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
7. To change column data type
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer;
ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.
fix :
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);
8. Know who is connected to the Database
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;
To know whether a User is Connected or Not
SELECT datname FROM pg_stat_activity WHERE usename = 'devuser';
9. Reloading PostgreSQL Configuration files without Restarting Server
select pg_reload_conf(); ==> some parameter changes need a full restart of the server to be take in effect.
10. To find data directory path
SHOW data_directory;
The same function can be used to change the data directory of the cluster, but it requires a server restarts:
SET data_directory to new_directory_path;
11. Finding missing values in a Sequence. use appropriate table_name and column name in the below query.
SELECT customer_id + 1 FROM customers mo WHERE NOT EXISTS(SELECT NULL FROM customers mi WHERE mi.customer_id = mo.customer_id + 1)ORDER BY customer_id;
12. To get the name of the seq. associated with customer_id:
SELECT pg_get_serial_sequence('customers', 'customer_id')
13. How to find invalid objects.
In Oracle, you can always ALTER an object (for example a table) even if there are dependent objects (for example views). The dependent objects then become invalid and have to be recompiled.
In PostgreSQL, you cannot ALTER an object that has dependend objects in a way that renders these dependent objects invalid. You have to drop and recreate the dependent objects.
The status column in dba_object is included for compatibility only, and it is always set to VALID.
14. How to find Invalid Indexes:
SELECT pg_class.relname FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
https://www.dbrnd.com/postgresql-dba-scripts/
15. Howto Check the version of PostgreSQL:
SELECT version();
16. How to Create a database using the script:
CREATE DATABASE Database_Name WITH OWNER User_Name;
17. How to Change the owner of the database:
ALTER DATABASE Database_Name OWNER TO User_Name;
18. Create a full copy of the database with structure and data:
CREATE DATABASE NewDB WITH TEMPLATE OldDB;
19. How to Create a database User using the script:
CREATE USER Anvesh WITH password 'testPassword';
20. How to Change the password of the User:
ALTER USER postgres WITH PASSWORD 'temproot';
21. How to Upgrade the existing User to Superuser:
ALTER USER TestUser WITH SUPERUSER;
22. How to Reload the PostgreSQL configuration file:
SELECT pg_reload_conf();
23. How to Rotate the PostgreSQL Log file:
SELECT pg_rotate_logfile();
24. How to Find the Postgres server start time:
SELECT pg_postmaster_start_time();
25. How to findour the timezone
postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
date_part | date_part
-----------+-----------
-4 | 0
postgres=# select now();
now
-------------------------------
2011-06-17 12:54:39.195291-04
dvdrental=# select now()::time;
now
-----------------
11:58:24.815151
(1 row)
26. How to create database link
Same Database, Two Different Schemas SCH1 & SCH2 then you can use View alternative to DB Link
If Cross Datbase Then, Use Foreign Data Wrapper to connect.
Ref :TestFor DB Link Creation in Postgress.txt
27. How to return top 5 biggest tables in the DB.
SELECT relname AS "relation", pg_size_pretty ( pg_total_relation_size (C .oid)) AS "total_size"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE nspname NOT IN ( 'pg_catalog', 'information_schema' ) AND C .relkind <> 'i' AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size (C .oid) DESC LIMIT 5;
28. How To get total size of all indexes attached to a table.
SELECT pg_size_pretty (pg_indexes_size('actor')); ===> You can pass OID also as the parameter.
29. How to get size of the Tablespace.
SELECT pg_size_pretty ( pg_tablespace_size ('<<tablespace_name>>'));
30. How to find how much space required to space specific value in Column.
^
postgres=# select pg_column_size('sdflkajsdfasjdf'::text);
pg_column_size
----------------
19
(1 row)
postgres=# select pg_column_size(23842392934::int2);
ERROR: smallint out of range
postgres=# select pg_column_size(23842392934::int4);
ERROR: integer out of range
postgres=# select pg_column_size(23842392934::int8);
pg_column_size
----------------
8
(1 row)
postgres=# select pg_column_size(2384239::int4);
pg_column_size
----------------
4
(1 row)
31. To get the size of each database in the current database server, you use the following statement:
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
32. How to set default search path for user.
postgres=# alter database "SAMPLE_DB2" set search_path to "S2_SCHEMA",public;
ALTER DATABASE
postgres=#
SAMPLE_DB2=# \d
List of relations
Schema | Name | Type | Owner
-----------+-------------------+-------+---------
S2_SCHEMA | from_s2_karthik | table | S2_USER
S2_SCHEMA | s2_employee_table | table | S2_USER
S2_SCHEMA | t2 | table | S2_USER
public | t1 | table | S2_USER
(4 rows)
34. How to take single table from particular DB
pg_dump -d SAMPLE_DB2 -t s2_employee_table > /vagrant/plaptop_pg1.txt
35. How to take multiple tables ?
Multiple tables can be selected by writing multiple -t switches"
Note that if you have several table with the same prefix (or suffix) you can also use wildcards to select them with the -t parameter:
use -T to exclude tables.
36. How to display or set time zone in PG.
dvdrental=# SHOW time zone;
TimeZone
----------
UTC
(1 row)
dvdrental=# SET TIME ZONE 'America/New_York';
SET
dvdrental=# SHOW time zone;
TimeZone
------------------
America/New_York
(1 row)
37. How to trim only time from date
dvdrental=# select now()::time;
now
-----------------
11:58:24.815151
(1 row)
dvdrental=# Select to_char(now()::Time, 'HH12:MI:SS AM');
to_char
-------------
12:02:30 PM
(1 row)
38. How to convert the time format ?
dvdrental=# select * from test1 where c is not null;
a | b | c
-------+----+----------
99.00 | 99 | 11:59:59
99.00 | 99 | 13:59:59
(2 rows)
dvdrental=# select a,b,c,to_char(c::Time, 'HH12:MI:SS AM') conv_format from test1 where c is not null;
a | b | c | conv_format
-------+----+----------+-------------
99.00 | 99 | 11:59:59 | 11:59:59 AM
99.00 | 99 | 13:59:59 | 01:59:59 PM
(2 rows)
dvdrental=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
a | numeric(5,2) | | |
b | numeric(4,0) | | |
c | time without time zone | | |
More Ref : https://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x2632_005.htm
39. How to load CSV files into pg.
a. Create the exact Same structure of table with proper format.
2. Use the below command..This below command will take records including header itself.
without Header :
db_dvdrental=> COPY sales15k FROM '/vagrant/sales15k.txt' ( FORMAT CSV);
COPY 1500000
With Header : But automaitcally skips the first row.
db_dvdrental=> COPY baby_born_50k FROM '/vagrant/baby_born.csv' delimiter ',' CSV HEADER ;
COPY 500000
40. How do you rename the column in the table
db_dvdrental=> alter table baby_born_50k alter column zip type int4;
ALTER TABLE
41. What are the stats view available or how do you monitor.
pg_stat_activity - information related to the current activity of connected clients and Postgres background processes.
pg_stat_database - database-wide statistics, such as number of commits/rollbacks, handled tuples, deadlocks, temporary files, etc.
pg_stat_replication - statistics on replication, connected standby hosts and their activity.
pg_stat_user_tables, pg_statio_user_tables - statistics on accesses (including IO) to tables.
pg_stat_user_indexes, pg_statio_user_indexes - statistics on accesses (including IO) to indexes.
pg_stat_user_functions - statistics on execution of functions.
pg_stat_statements - statistics on SQL statements executed including time and resources usage.
statistics on tables sizes based on pg_relation_size() and pg_total_relation_size() functions;
pg_stat_progress_vacuum - information about (auto)vacuums status.
Ref : https://github.com/lesovsky/pgcenter
42. How do you see the settings ?
select name,setting,short_desc,context from pg_settings where name = 'max_connections';
44. How do you find out whether db requires restart or not after changing config changes.
SELECT * FROM pg_settings WHERE pending_restart = true;
45. How do you find out Lag between Replication.
postgreSQL 10:
SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;
Previous Versions:
SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;
46. Where are the files for a PostgreSQL database stored?
[vagrant@pgnode1 ~]$ sudo -u postgres psql -c "show data_directory;"
could not change directory to "/home/vagrant": Permission denied
data_directory
------------------------
/var/lib/pgsql/10/data
(1 row)
or
ps aux | grep postgres | grep -- -D
postgres 1230 0.0 1.2 398396 22436 ? Ss 20:31 0:00 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data/
To see all the run-time parameters, use
show all;
47. To find tablespace
select * from pg_tablespace
48. How do you create tablespace
postgres=# create tablespace ldvdr_ts1 owner ldvdr_user location '/var/lib/pgsql/cust_ts/';
CREATE TABLESPACE
postgres=# \dt
Did not find any relations.
postgres=# select * from pg_tablespace;
spcname | spcowner | spcacl | spcoptions
------------+----------+--------+------------
pg_default | 10 | |
pg_global | 10 | |
ldvdr_ts1 | 16385 | |
(3 rows)
postgres=#
Note ; you can either create tablespace with ownership or create ts and assign to ownewr later. (default owner will be postgres )
eg : create user ldvdr_user;
eg ; CREATE TABLESPACE ldvdr_ts1
OWNER ldvdr_user
LOCATION '/var/lib/pgsql/10/data/';
ALTER TABLESPACE ldvdr_ts1
OWNER TO ldvdr_user;
49. how do you create your own database on pg.
postgres=# CREATE DATABASE db_dvdrental
postgres-# WITH
postgres-# OWNER = ldvdr_user
postgres-# ENCODING = 'UTF8'
postgres-# LC_COLLATE = 'en_US.UTF-8'
postgres-# LC_CTYPE = 'en_US.UTF-8'
postgres-# TABLESPACE = ldvdr_ts1
postgres-# CONNECTION LIMIT = -1;
CREATE DATABASE
postgres=# COMMENT ON DATABASE db_dvdrental IS 'karthik dvdrental administrative connection database' OWNER LDVDR_USER;
COMMENT
postgres=#
50. How do you revoke public access from database ?
REVOKE CONNECT ON DATABASE DB_DVDRENTAL FROM PUBLIC;
NOTE : You can create db from other db..But it will create outside of the db only..But by default it will connect privilege to PUBLIC Role
By default public role is assigned to all the users in the db cluster. so who oever having public role they can connect the new db.
so use revoke command for security.
Note : ALTERNATIVELY, YOU CAN CREATE DB OUTSIDE OF PSQL
createdb db1 ==> createdb is an executable
Note : It looks ts is equal to system / sysaux like oracle.
51. to find list of databases
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------+------------+----------+-------------+-------------+-----------------------
db_dvdrental | ldvdr_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
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
(4 rows)
52. how to create schema to hold objects ?
postgres=# \q
bash-4.2$ psql -d db_dvdrental
psql (10.4)
Type "help" for help.
db_dvdrental=# create schema ldvdr_schema authorization ldvdr_user;
CREATE SCHEMA
db_dvdrental=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+------------
ldvdr_user | Superuser, Create role, Create DB, Replication | {postgres}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
db_dvdrental=# \dn
List of schemas
Name | Owner
--------------+------------
ldvdr_schema | ldvdr_user
public | postgres
(2 rows)
db_dvdrental=#
53. how to load sample pg data into db_dvdrental database ?
pg_restore -U ldvdr_user -d db_dvdrental /vagrant/pg_dvdr_sample/dvdrental.tar
db_dvdrental=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | actor | table | postgres
public | address | table | postgres
public | category | table | postgres
public | city | table | postgres
public | country | table | postgres
public | customer | table | postgres
public | film | table | postgres
public | film_actor | table | postgres
public | film_category | table | postgres
public | inventory | table | postgres
public | language | table | postgres
public | payment | table | postgres
public | rental | table | postgres
public | staff | table | postgres
public | store | table | postgres
(15 rows)
db_dvdrental=#
54. How to restore sample db dvdrental to your custom database name called db_dvdrental database.
Unzip dvdrental.tar file.
Edit restore.sql -> Change public to your ldvdr_schema ( This is not user name. This is like Tablespace )
Change the lines to the user. By default the search path will be public schema. we should change schema and whom should we give permission for.
GRANT ALL ON SCHEMA ldvdr_schema TO ldvdr_user;
Note : a. Trying to store the database thru pg_restore does not work. Alternative way , use the below commands
cd << Location >>
psql -echo-all --file=restore.sql db_dvdrental
55. How to view and modify the permission of tables,vies,sequences,types ?
Tables :
--------
SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO my_new_owner;' FROM pg_tables
WHERE NOT schemaname IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;
Sequences :
-----------
SELECT 'ALTER SEQUENCE '|| sequence_schema || '.' || sequence_name ||' OWNER TO my_new_owner;' FROM information_schema.sequences
WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema') ORDER BY sequence_schema, sequence_name;
Views :
-------
SELECT 'ALTER VIEW '|| table_schema || '.' || table_name ||' OWNER TO my_new_owner;' FROM information_schema.views
WHERE NOT table_schema IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name;
Information_schema having 67 Catalog Objects ..it can be accessed thru information_schema.<<name>>
56. How to describe objects. << Schema_name.Object_Name>>
db_dvdrental=# \d+ ldvdr_schema.actor
Table "ldvdr_schema.actor"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+------------------------------------------------------+----------+--------------+-------------
actor_id | integer | | not null | nextval('ldvdr_schema.actor_actor_id_seq'::regclass) | plain | |
first_name | character varying(45) | | not null | | extended | |
last_name | character varying(45) | | not null | | extended | |
last_update | timestamp without time zone | | not null | now() | plain | |
Indexes:
"actor_pkey" PRIMARY KEY, btree (actor_id)
"idx_actor_last_name" btree (last_name)
Referenced by:
TABLE "ldvdr_schema.film_actor" CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES ldvdr_schema.actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
last_updated BEFORE UPDATE ON ldvdr_schema.actor FOR EACH ROW EXECUTE PROCEDURE ldvdr_schema.last_updated()
db_dvdrental=#
57. To find list of schemas
db_dvdrental=# \dn
List of schemas
Name | Owner
--------------+----------
ldvdr_schema | postgres
public | postgres
58. To find list of tablespaces
db_dvdrental=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------------+------------+--------------------------+------------------------------
ldvdr_schema | ldvdr_user | ldvdr_user=UC/ldvdr_user | Standard ldvdr_schema schema
public | postgres | postgres=UC/postgres +| standard public schema
| | =UC/postgres |
(2 rows)
59. how to rename the owner of the schema ?
db_dvdrental=# alter schema ldvdr_schema owner to ldvdr_user;
ALTER SCHEMA
db_dvdrental=# \dn
List of schemas
Name | Owner
--------------+------------
ldvdr_schema | ldvdr_user
public | postgres
(2 rows)
60. What are the basic commands ?
\l => list of dbs or select datname from pg_database;
You can find outside of pgsql using the command : psql META COMMAND \l
\du -> To find list of users.
\dt => List of tables for the connected users
\dv -> list of view
\ds => list of sequences.
\dn => list of schemas
\db => list of tablespaces.
61. how to take single schema backup
-n schema
--schema=schema
bash-4.2$ pg_dump --schema=ldvdr_schema db_dvdrental > ldvdr_schema_backup.sql
bash-4.2$ ls -l ldvdr_schema_backup.sql
-rw-r--r--. 1 postgres postgres 46493 Jul 31 20:45 ldvdr_schema_backup.sql
bash-4.2$
Note : It provides only metadata details Not exporting Data
https://www.chrisnewland.com/postgres-dump-database-schema-with-or-without-data-61
62. How to copy the database schema (table structure) with or without the table data from an existing postgres database?
pg_dump -Cs DB_NAME > /tmp/schema_dump
-C switch adds the CREATE statements
-s switch specifies dumping only the schema not the data
If you want the data as well then use
pg_dump -C DB_NAME > /tmp/schema_dump
If you want ONLY the data and not the database or table CREATE statements then use pg_dump -a DB_NAME > /tmp/data_dump
All of the above dump the data as COPY commands which are faster but may not be compatible with other database engines.
If you want to dump data from postgres into another database then dump as INSERT statements by using
pg_dump -D DB_NAME > /tmp/data_dump_as_inserts
This uses the INSERT INTO table (col1, col2) VALUES (val1, val2) format. Using a lowercase "d" omits the column mappings for a slight increase in speed if you know the table is structured in the same way on the target database.
To restore the data:
63. How to count the number of tables
db_dvdrental=# table table_name
Note : Everything in PG related with Obj ID.
64. How to find where is the object stored physically in pg.
^
db_dvdrental=# select pg_relation_filepath('ldvdr_schema.staff');
pg_relation_filepath
---------------------------------------------
pg_tblspc/16387/PG_10_201707211/16388/16881
(1 row)
^
db_dvdrental=# select oid , relname from pg_class where oid=16881;
oid | relname
-------+---------
16881 | staff
(1 row)
Note : Database Objects related like table,constraint,Index,view,Sequences you can check pg_class to get object ids.
65. how do you switch from one db to another db in postgres
postgres=# \c db_dvdrental
You are now connected to database "db_dvdrental" as user "postgres".
db_dvdrental=#
Note : dont use schema user like oracle to select the query. Use schema name.
db_dvdrental=# select count(*) from ldvdr_user.film;
ERROR: relation "ldvdr_user.film" does not exist
LINE 1: select count(*) from ldvdr_user.film;
^
db_dvdrental=# select count(*) from ldvdr_schema.film;
count
-------
0
(1 row)
db_dvdrental=#
you can set ldvdr_schema in set search_path to avoid schema name before that object name.
66. How do you describe ?
db_dvdrental=# \d ldvdr_schema.film
Table "ldvdr_schema.film"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+----------------------------------------------------
film_id | integer | | not null | nextval('ldvdr_schema.film_film_id_seq'::regclass)
title | character varying(255) | | not null |
description | text | | |
release_year | ldvdr_schema.year | | |
language_id | smallint | | not null |
rental_duration | smallint | | not null | 3
rental_rate | numeric(4,2) | | not null | 4.99
length | smallint | | |
replacement_cost | numeric(5,2) | | not null | 19.99
rating | ldvdr_schema.mpaa_rating | | | 'G'::ldvdr_schema.mpaa_rating
last_update | timestamp without time zone | | not null | now()
special_features | text[] | | |
fulltext | tsvector | | not null |
Indexes:
"film_pkey" PRIMARY KEY, btree (film_id)
"film_fulltext_idx" gist (fulltext)
"idx_fk_language_id" btree (language_id)
"idx_title" btree (title)
Foreign-key constraints:
"film_language_id_fkey" FOREIGN KEY (language_id) REFERENCES l
SELECT * FROM information_schema.columns where table_name='film';
It shows records only the column names ..Not with data.
select * from schemaname.object_name works well.
67. How do you find the current data directory from pg.
db_dvdrental=# show data_directory;
data_directory
------------------------
/var/lib/pgsql/10/data
(1 row)
68. how do you find out whether the parameter change requires reboot or reload
reboot - all the sessions are disconnected
reload - Not all sessions disconnected ..( which parameters does not require reboot )
ans : check postgresql.conf or pg_settings view
69. How do you reload the server or only config reload ?
pg_ctl -> can be used to reload ( reload from postgresql.conf,pg_hba.conf,ident config )
pg_ctl reload [options] or pg_reload_conf() function
pt_ctl restart -> PG Server restart ( like bounce )
pg_ctl => You can not stop one db on the cluster. Stops everything.
--smart -> waits to quit all clients
-- fast -> quit directly with proper shutdown (default )
--> immediate -> quits without shutdown equal to oracle abort; it leads to recov mdoe.
bash-4.2$ ./pg_ctl -D /var/lib/pgsql/10/data stop
waiting for server to shut down.... done
server stopped
bash-4.2$ pwd
/usr/pgsql-10/bin
bash-4.2$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
bash-4.2$
bash-4.2$ ps -ef|grep -i post
root 8071 7930 0 19:27 pts/1 00:00:00 sudo su postgres
root 8072 8071 0 19:27 pts/1 00:00:00 su postgres
postgres 8073 8072 0 19:27 pts/1 00:00:00 bash
postgres 8141 8073 0 19:28 pts/1 00:00:00 ps -ef
postgres 8142 8073 0 19:28 pts/1 00:00:00 grep -i post
bash-4.2$
70. how do you create pg cluster ?
-bash-4.2$ which initdb
/usr/pgsql-10/bin/initdb
-bash-4.2$ which pg_ctl
/usr/pgsql-10/bin/pg_ctl
-bash-4.2$ initdb -D /u01/pgdata01
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /u01/pgdata01 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /u01/pgdata01 -l logfile start
-bash-4.2$ pwd
/u01/pgdata01
-bash-4.2$ ls -lrt
total 48
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_serial
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_dynshmem
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_commit_ts
-rw-------. 1 postgres postgres 3 Aug 6 19:37 PG_VERSION
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_twophase
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_tblspc
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_stat_tmp
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_stat
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_snapshots
drwx------. 2 postgres postgres 6 Aug 6 19:37 pg_replslot
drwx------. 4 postgres postgres 36 Aug 6 19:37 pg_multixact
-rw-------. 1 postgres postgres 22761 Aug 6 19:37 postgresql.conf
-rw-------. 1 postgres postgres 88 Aug 6 19:37 postgresql.auto.conf
-rw-------. 1 postgres postgres 4513 Aug 6 19:37 pg_hba.conf
-rw-------. 1 postgres postgres 1636 Aug 6 19:37 pg_ident.conf
drwx------. 2 postgres postgres 18 Aug 6 19:37 pg_xact
drwx------. 3 postgres postgres 60 Aug 6 19:37 pg_wal
drwx------. 2 postgres postgres 18 Aug 6 19:37 pg_subtrans
drwx------. 2 postgres postgres 18 Aug 6 19:37 pg_notify
drwx------. 2 postgres postgres 4096 Aug 6 19:37 global
drwx------. 5 postgres postgres 41 Aug 6 19:37 base
drwx------. 4 postgres postgres 68 Aug 6 19:37 pg_logical
-bash-4.2$
Note : If you want to create multiple pg cluster, edit postgresql ==> Port number and run pg_ctl after that.
-bash-4.2$ pg_ctl -D /u01/pgdata01 -l logfile start
waiting for server to start.... done
server started
-bash-4.2$ ps -ef|grep -i post
root 8624 5916 0 19:36 pts/1 00:00:00 sudo su - postgres
root 8625 8624 0 19:36 pts/1 00:00:00 su - postgres
postgres 8626 8625 0 19:36 pts/1 00:00:00 -bash
postgres 9253 1 0 19:47 pts/1 00:00:00 /usr/pgsql-10/bin/postgres -D /u01/pgdata01
postgres 9254 9253 0 19:47 ? 00:00:00 postgres: logger process
postgres 9256 9253 0 19:47 ? 00:00:00 postgres: checkpointer process
postgres 9257 9253 0 19:47 ? 00:00:00 postgres: writer process
postgres 9258 9253 0 19:47 ? 00:00:00 postgres: wal writer process
postgres 9259 9253 0 19:47 ? 00:00:00 postgres: autovacuum launcher process
postgres 9260 9253 0 19:47 ? 00:00:00 postgres: stats collector process
postgres 9261 9253 0 19:47 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 9271 8626 0 19:47 pts/1 00:00:00 ps -ef
postgres 9272 8626 0 19:47 pts/1 00:00:00 grep --color=auto -i post
-bash-4.2$
71. How do you view the cluster control information..?
How do you find out latest check point?
How do you find out prior checkpoint
how do you find out the timelineID
pg_controldata [DATADIR]
bash-4.2$ /usr/pgsql-11/bin/pg_controldata /var/lib/pgsql/11/data
pg_control version number: 1100
Catalog version number: 201809051
Database system identifier: 6717253964587648896
Database cluster state: in production
pg_control last modified: Tue 06 Aug 2019 03:32:23 PM EDT
Latest checkpoint location: 0/70001FC0
Latest checkpoint's REDO location: 0/70001F88
Latest checkpoint's REDO WAL file: 000000010000000000000070
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:889
Latest checkpoint's NextOID: 17326
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 561
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 889
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Tue 06 Aug 2019 03:32:21 PM EDT
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 2539c5323b2e0a93cbd7d4170ec2aa8090e48e84b21545f046d7301c051a5e6c
72. How do you view the dynamic parameter changes and static parameter changes.
pg_settings -> Dynamic parameter settings.
pg_file_settings -> Retrieves data from config files.
73. How do you to get help from the psql prompt
use the below format
eg :
psql: \h CREATE DATABASE
While dropping the db, nobody should be connected.
createdb --help ( to create db outside of psql)
74. How do you provide grants to user for particular schema
grant usage on schema <schenaname> to user;
75. How to get the command history and store it in file / What are the meta commands ?
Comments
Post a Comment