For a particular table,
SELECT pg_size_pretty( pg_total_relation_size('tablename') );
For a particular database,
SELECT pg_size_pretty( pg_database_size('dbname') );
$ psql -U postgres -c 'SHOW config_file'
COPY (SELECT "dbname".* FROM "tablename")
TO '/tmp/output-file.csv'
WITH DELIMITER ';' CSV HEADER
COPY tablename
FROM '/tmp/input-file.csv'
REVOKE ALL ON DATABASE example_database FROM example_user;
GRANT CONNECT ON DATABASE example_database TO example_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO example_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO example_user;
# Grant all privileges on all tables to a particular user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO example_user;
# Grant all privileges on all tables (both existing and new tables) to a certain user
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO example_user;
# View a user's grants
SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'example_user' ORDER BY table_name ASC;
OR
GRANT USAGE ON SCHEMA public TO my_db_user;
GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO my_db_user;
-- If you have sequences
GRANT SELECT, UPDATE, USAGE ON ALL SEQUENCES IN SCHEMA public to my_db_user;
-- If you have functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO my_db_user;
-- Cater for future tables that will be created
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLES TO my_db_user;
Check that you have the pg_stat_statements extension installed
postgres=# \x
postgres=# \dx pg_stat_statements
If you dnn't obtain any result, then issue the following command:
postgres=# CREATE EXTENSION pg_stat_statements;
postgres=# ALTER SYSTEM
SET shared_preload_libraries = 'pg_stat_statements';
Then, restart the server
You can get the top ten highest workloads on your server side by executing the following:
postgres=# SELECT calls, total_time, query FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;
There are many additional columns that are useful in tracking down further information about particular entries
postgres=# \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Modifiers
---------------------+------------------+-----------
userid | oid |
dbid | oid |
queryid | bigint |
query | text |
calls | bigint |
total_time | double precision |
min_time | double precision |
max_time | double precision |
mean_time | double precision |
stddev_time | double precision |
rows | bigint |
shared_blks_hit | bigint |
shared_blks_read | bigint |
shared_blks_dirtied | bigint |
shared_blks_written | bigint |
local_blks_hit | bigint |
local_blks_read | bigint |
local_blks_dirtied | bigint |
local_blks_written | bigint |
temp_blks_read | bigint |
temp_blks_written | bigint |
blk_read_time | double precision |
blk_write_time | double precision |
postgres=# ALTER SYSTEM
SET log_min_duration_statement = 10000;
ALTER TABLE IF EXISTS table_name
RENAME TO new_table_name;
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'db_name'
ORDER BY 1;
or if you are using the psql:
# \l db_name
Assuming the table my_table already exists,
\copy my_table (col1, col2, col3, ...) FROM '/path/to/csv-file.csv' CSV HEADER;
$ pg_dump <dbname> -U <dbuser> -f <filename>.sql
# If you want to compress the database dump
$ pg_dump <dbname> -U <dbuser> | gzip > <filename>.sql.gz
- Create an empty database or clear out the existing database
# To clear out an existing database/schema
$ psql -U <dbuser> -d <dbname> -c "DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;"
- Gunzip the copy, if you created a compressed version
- Run the following command to restore:
$ psql -d <dbname> -U <dbuser> -f <filename>.sql
SELECT *
FROM words
WHERE difficulty = 'Easy' AND category_id = 3
ORDER BY random()
LIMIT 1;
SELECT * FROM information_schema.tables WHERE table_schema = 'public';
Alternatively,
For all schemas
=> \dt *.*
For a particular schema,
=> \dt public.*
SHOW hba_file;
SHOW config_file;
su - postgres
/usr/bin/pg_ctl reload
Alternatively, you can use SQL
SELECT pg_reload_conf();
SELECT convert_from(decode(<bytea_column>, 'escape'), 'UTF-8') FROM <table_name>;
From the command line,
psql --versionAlternatively, from PSQL program
SELECT version(); # postgres version
SELECT PostGIS_full_version(); # postgis version19. 🔗 Create PostgreSQL User
To create a postgreSQL user with a create database permission only, you can use any of the approaches below:
# Without password
CREATE ROLE myuser;
ALTER ROLE myuser WITH CREATEDB;
# with password
CREATE ROLE myuser;
ALTER ROLE myuser WITH LOGIN ENCRYPTED PASSWORD 'somepassword' CREATEDB;Alternatively,
# Without password
CREATE ROLE myuser CREATEDB;
# With password (Option 1)
CREATE ROLE myuser WITH LOGIN ENCRYPTED PASSWORD 'somepassword' CREATEDB;
# With password and superuser privileges (Option 2)
CREATE USER myuser WITH SUPERUSER CREATEDB LOGIN ENCRYPTED PASSWORD 'somepassword';If
CREATEDBis specified, the created user will be allowed to create their own databases. UsingNOCREATEDBwill deny the user the ability to create databases. If not specified,NOCREATEDBis the default
To change the password of a Postgres user:
- Login to Postgres without a password
sudo -u <user_name> psql db_name
For example,
sudo -u postgres psql dhis2
- Reset the password
ALTER USER <user_name> WITH PASSWORD '<new_password>';
Alternatively,
sudo -u postgres psql
\password postgres
To hide the column names included as part of the query resultset in psql, you can pass the -t or --tuples-only flag to psql:
$ psql --user=dbuser -d mydb -t -c "SELECT count(*) FROM dbtable;"
SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = 'db-name-in-lower-case';
Alternatively from the command line,
psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'db-name'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE db-name"
SELECT 1 FROM pg_roles WHERE rolname='the-postgres-username'
If you'd rather run it from the command line:
psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='the-postgres-username'"
On unix, you can use grep to chain multiple commands:
psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='the-postgres-username'" | grep -q 1 || createuser ...
SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'schema-name';
If you will like to create a schema if one doesn't already exist,
CREATE SCHEMA IF NOT EXISTS `schema-name`;
Likewise for Postgres extensions:
CREATE EXTENSION IF NOT EXISTS `extension-name`;
SELECT name, setting FROM pg_settings;
SELECT setting FROM pg_settings WHERE name = 'max_locks_per_transaction';SELECT column_name FROM information_schema.columns WHERE table_name='the_table_name' and column_name='the_column_name';
# OR
SELECT column_name FROM information_schema.columns WHERE table_name='the_table_name' and column_name='the_column_name';
Alternatively, you can adapt the query to return true/false:
SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='my_schema_name' AND table_name='my_table_name' AND column_name='my_column_name');postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 2.4.4 | postgis | PostGIS geometry, geography, and raster spatial types and functions
(2 rows)
To get more details,
postgres=# \dx+ plpgsql
Objects in extension "plpgsql"
Object Description
-------------------------------------------
function plpgsql_call_handler()
function plpgsql_inline_handler(internal)
function plpgsql_validator(oid)
language plpgsql
(4 rows)
Alternatively,
SELECT * FROM pg_extension;
To get the list of all available extensions on your server,
SELECT * FROM pg_available_extensions;
- Using SQL Query
You can get the list of all schemas using SQL with the ANSI standard of INFORMATION_SCHEMA:
SELECT schema_name FROM information_schema.schemata
or
SELECT nspname FROM pg_catalog.pg_namespace;
Information schema is simply a set of views of pg_catalog.
- Using psql
\dn
29. 🔗 Drop User Role If Exists
DROP ROLE IF EXISTS bambini;
DROP FUNCTION IF EXISTS My_Function(TEXT, INT);
Postgres functions can be overloaded, therefore specifying the parameter types are necessary to reduce ambiguity while differentiating between overloaded functions.
ALTER DATABASE <db-name> OWNER TO <new-owner-name>;
SELECT datname, pg_user.usename as owner_username
FROM pg_database
JOIN pg_user ON pg_database.datdba = pg_user.usesysid
WHERE datname = '<db-name>';
CREATE DATABASE <new-db-name>
WITH TEMPLATE <name-of-db-to-be-copied>
OWNER <db-owner>;
If the database to be copied is being accessed by any user, all connections to it may have to be terminated before you can create a copy of that database. You can achieve this with:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '<name-of-db-to-be-copied>'
AND pid <> pg_backend_pid();
After which you can proceed with the database copy command provided previously.
Check the all active connections to the database before the rename:
SELECT *
FROM pg_stat_activity
WHERE datname = '<current-db-name>';
Terminate all active connections, if any:
SELECT
pg_terminate_backend (pid)
FROM
pg_stat_activity
WHERE
datname = '<current-db-name>';
Proceed with the rename once this is done:
ALTER DATABASE <current-db-name> RENAME TO <new-db-name>;
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = '<role>';
SELECT
nspname || '.' || relname AS "Table",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "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;