How to connect to postgres server?
psql -h xxx -U xxx -d
Create a .pgpass file in which you can store the password of the user.
How to Get table size?
first you have to run ANALYZE.
Option 1 (overall):
select
pg_relation_size(20306, 'main') as main,
pg_relation_size(20306, 'fsm') as fsm,
pg_relation_size(20306, 'vm') as vm,
pg_relation_size(20306, 'init') as init,
pg_table_size(20306),
pg_indexes_size(20306) as indexes,
pg_total_relation_size(20306) as total;
Option 2 (specific table):
SELECT ROUND(pg_total_relation_size (oid) / ( 1024.0 * 1024.0 ), 2) AS
"Size in MB"
FROM pg_class c
WHERE relname = 'sbtest1';
Option 3 (partitioned tables):
SELECT
pi.inhparent::regclass AS parent_table_name,
pg_size_pretty(sum(pg_total_relation_size(psu.relid))) AS total,
pg_size_pretty(sum(pg_relation_size(psu.relid))) AS internal,
pg_size_pretty(sum(pg_table_size(psu.relid) - pg_relation_size(psu.relid))) AS external, -- toast
pg_size_pretty(sum(pg_indexes_size(psu.relid))) AS indexes
FROM pg_catalog.pg_statio_user_tables psu
JOIN pg_class pc ON psu.relname = pc.relname
JOIN pg_database pd ON pc.relowner = pd.datdba
JOIN pg_inherits pi ON pi.inhrelid = pc.oid
WHERE pd.datname = :database_name
GROUP BY pi.inhparent
ORDER BY sum(pg_total_relation_size(psu.relid)) DESC;
Option 4 (for all non-partitioned tables):
SELECT relname, ROUND(pg_total_relation_size (oid) / ( 1024.0 * 1024.0 ), 2) AS "Size in MB"
FROM pg_class c
order by "Size in MB" DESC;
How to Create User?
postgres=> CREATE USER sysbench WITH ENCRYPTED PASSWORD '2O3vdR4F';
CREATE ROLE
postgres=> GRANT ALL PRIVILEGES ON DATABASE sysbench TO sysbench;
How to Grant read privilege on tables?
grant SELECT on all tables in schema public to someuser;
The issue with this command is that it will grant read privileges on the existing tables but not on future tables.
How to Alter default privileges?
To grant SELECT privilege to everyone for all tables (and views) you subsequently create in schema myschema, and allow role webuser to INSERT into them too:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
Check user privileges
select * from information_schema.role_table_grants where grantee='username';
SELECT * FROM information_schema.table_privileges WHERE grantee = 'username';
How to List users and their roles?
\du
\dp
List Databases
\l
List Schemas
\dn
The schema exists within a database. Above will show schemas in current database
List Tables
\d
\dt
Describe table
\d sometable
How to see currently executing queries
select * from pg_stat_activity where usename='postgres';
Select a database
\c somedatabase
View command history
\s
Extract table as CSV file
\copy (select _from sometable) to 'sometable.csv' with csv header quote '"' force quote_
Load data from CSV file into Postgres
First you have to create the table. Then:
\copy sometable
FROM 'sometable.csv'
with DELIMITER ','
csv
header
quote '"'
How to see table schema (show create table of MySQL)
show create table is a MySQL command that gives the command that can be used to re-create a table.
In case of postgres, we have to use pg_dump:
pg_dump --table some_table --schema-only
pg_dump -t 'schema-name.table-name' --schema-only database-name
Check shared buffer size
postgres=> show shared_buffers;
shared_buffers
----------------
10704MB
(1 row)
How to get database size?
SELECT pg_size_pretty(pg_database_size('database_name'));
How to view database settings?
SELECT name, setting FROM pg_settings;
How to measure time?
\timing on
How to change password?
ALTER USER user_name WITH PASSWORD 'new_password';
Run last command continuously
\watch
Check if table contains a foreign key constraint
SELECT
tc.table_schema,
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
Get row count of all tables
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
List tables I own
SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public'
AND tableowner = current_user;
Show all indexes
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;
This does not list indexes created as a result of constraints on the tables.
the pg_indexes table only includes indexes that have been created explicitly with a CREATE INDEX statement. It does not include indexes created automatically by PostgreSQL to enforce constraints or improve query performance.
List all constraints
SELECT conname, conrelid::regclass, contype, condeferrable, condeferred, convalidated
FROM pg_constraint
ORDER BY conrelid::regclass::text, contype DESC;
See long running queries
create extension pg_stat_statements;
SELECT query, calls, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC;
Save output to a file (very useful)
Step 1:
\o out.txt
Step 2:
run your command
Step 3:
\o
this undoes Step 1. the output will now go to console.
Connect to Google Cloud SQL using IAM
PGPASSWORD=$(gcloud sql generate-login-token) psql \
"sslmode=verify-ca \
sslrootcert=/path/to/root/cert \
sslcert=/path/to/client/cert.pem \
sslkey=/path/to/client/key.pem \
hostaddr=x.x.x.x \
user=$EMAIL \
dbname=$SOME_DATABASE"
pg_dump using IAM credentials
export PGPASSWORD=$(gcloud sql generate-login-token)
pg_dump \
"sslmode=verify-ca \
sslrootcert=/path/to/root/cert \
sslcert=/path/to/client/cert \
sslkey=/path/to/client/key.pem \
hostaddr=x.x.x.x \
user=$EMAIL \
dbname=$SOME_DATABASE" \
--table=$SOME_TABLE \
--schema-only
siddharth's space 