Postgresql - Revision history

T1t0: /* System query */

2025-12-09T16:15:14Z

System query

@@ Line 50: / Line 50: @@
 postgres@host:~$ psql
 postgres=# SELECT usename FROM pg_user; # or \dg
 postgres=# \q
 </pre>

T1t0: /* Create user */

2025-11-16T20:55:02Z

Create user

← Older revision		Revision as of 20:55, 16 November 2025
Line 255:		Line 255:
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres=# CREATE ROLE superuser WITH LOGIN PASSWORD '~~superuser~~' SUPERUSER;		postgres=# CREATE ROLE superuser WITH LOGIN PASSWORD 'superuser_pass' SUPERUSER;
			</pre>

			=== Create a backup user ===

			On modern postgresql, you can create an user with read access to all databases:
			<pre>
			CREATE ROLE backup WITH IN ROLE pg_read_all_data LOGIN PASSWORD 'backup_pass';
	</pre>		</pre>

Line 263:		Line 270:
	root@host:~# su - postgres		root@host:~# su - postgres
	postgres@host:~$ psql -U postgres postgres		postgres@host:~$ psql -U postgres postgres
	postgres=# CREATE ROLE readonly_user LOGIN PASSWORD ~~XXX~~;		postgres=# CREATE ROLE readonly_user LOGIN PASSWORD 'readonly_pass';
	postgres=# GRANT CONNECT ON DATABASE database_to_query TO readonly_user;		postgres=# GRANT CONNECT ON DATABASE database_to_query TO readonly_user;
	postgres=# \q		postgres=# \q

T1t0: /* Backup and restore */

2024-04-03T07:17:32Z

Backup and restore

← Older revision		Revision as of 07:17, 3 April 2024
Line 435:		Line 435:
	</pre>		</pre>

	~~=== Just~~ the database ~~users/roles ===~~		* Note: a better (secure) way is use the owner of database

	'''on source host'''		'''on source host'''
	<pre>		<pre>
	postgres@host_src:~$ ~~pg_dumpall -h localhost~~ -U postgres name_db --~~globals-only~~ --~~clean --if-exists > dumpall_globalsonly~~.sql		postgres@host_src:~$ pg_dump -U postgres name_db --no-owner > dbname-dump.dump.sql
	</pre>		</pre>

	~~note: it contains the postgres user permission too, you can avoid changes to such user removing the related lines in the dump:~~		'''on destination host'''
	<pre>		<pre>
	~~sed~~ -~~i '/^DROP ROLE.postgres/~~d~~;/^CREATE ROLE.postgres/d;/^ALTER ROLE~~.*postgres/d' dumpall_globalsonly.sql		postgres@host_dst:~$ psql -h localhost -U db_user -d name_db -E < dbname-dump.dump.sql
	</pre>		</pre>

	'''on ~~destination~~ host'''
			=== Just the database users/roles ===
			'''on source host'''
	<pre>		<pre>
	postgres@~~host_dst~~:~$ ~~psql -E --set ON_ERROR_STOP=on~~ -h localhost -U postgres -~~d postgres~~ -f dumpall_globalsonly.sql		postgres@host_src:~$ pg_dumpall -h localhost -U postgres name_db --globals-only --clean --if-exists > dumpall_globalsonly.sql
	</pre>		</pre>

			note: it contains the postgres user permission too, you can avoid changes to such user removing the related lines in the dump:
	* Note: ~~a better (secure) way is use~~ the ~~owner of database~~

	~~'''on source host'''~~
	<pre>		<pre>
	postgres~~@host_src:~$ pg_dump -U~~ postgres ~~name_db --no-owner > dbname-dump~~.~~dump~~.sql		sed -i '/^DROP ROLE.postgres/d;/^CREATE ROLE.postgres/d;/^ALTER ROLE.*postgres/d' dumpall_globalsonly.sql
	</pre>		</pre>

	'''on destination host'''		'''on destination host'''
	<pre>		<pre>
	postgres@host_dst:~$ psql -h localhost -U ~~db_user~~ -d ~~name_db -E < dbname~~-~~dump.dump~~.sql		postgres@host_dst:~$ psql -E --set ON_ERROR_STOP=on -h localhost -U postgres -d postgres -f dumpall_globalsonly.sql
	</pre>		</pre>

T1t0: /* Show connections */

2024-01-16T07:59:46Z

Show connections

← Older revision		Revision as of 07:59, 16 January 2024
Line 133:		Line 133:
	* count connections by user		* count connections by user
	<pre>		<pre>
	~~select~~ usename, count(*) as count ~~from~~ pg_stat_activity ~~group by~~ usename ~~order by~~ count ~~desc~~;		SELECT usename, count(*) AS count FROM pg_stat_activity GROUP BY usename ORDER BY count DESC;
	</pre>		</pre>

T1t0: /* Show connections */

2024-01-16T07:59:09Z

Show connections

← Older revision		Revision as of 07:59, 16 January 2024
Line 123:		Line 123:
	== Show connections ==		== Show connections ==

			* show connection info
	<pre>		<pre>
	SELECT * FROM		SELECT * FROM
Line 128:		Line 129:
	(SELECT setting::int res_for_super FROM pg_settings WHERE name='superuser_reserved_connections') q2,		(SELECT setting::int res_for_super FROM pg_settings WHERE name='superuser_reserved_connections') q2,
	(SELECT setting::int max_conn FROM pg_settings WHERE name='max_connections') q3;		(SELECT setting::int max_conn FROM pg_settings WHERE name='max_connections') q3;
			</pre>

			* count connections by user
			<pre>
			select usename, count(*) as count from pg_stat_activity group by usename order by count desc;
	</pre>		</pre>

T1t0 at 09:27, 4 January 2024

2024-01-04T09:27:12Z

← Older revision		Revision as of 09:27, 4 January 2024
Line 94:		Line 94:
	</pre>		</pre>

	== ~~List~~ table ~~columns~~ ==		=== Show table size ===

	<pre>		<pre>
	root@host:~# su - postgres		root@host:~# su - postgres
	postgres@host:~$ psql -U postgres ~~-d name_db~~		postgres@host:~$ psql -U postgres
	postgres=# SELECT ~~column_name FROM information_schema.columns WHERE table_name =~~'db_table'; ~~# or \d db_table~~		postgres=# SELECT pg_size_pretty(pg_total_relation_size('db_table'));
	postgres=# \q		postgres=# \q
	</pre>		</pre>

	=== Show ~~table size~~ ===		=== Show all tables sizes ===

	<pre>		<pre>
	root@host:~# su - postgres		root@host:~# su - postgres
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres=# SELECT pg_size_pretty(pg_total_relation_size('~~db_table~~'));		postgres=# SELECT table_catalog AS database, table_schema \|\| '.' \|\| table_name AS name, pg_size_pretty(pg_total_relation_size(table_schema \|\| '.' \|\| table_name)) AS size FROM information_schema.tables ORDER BY pg_total_relation_size(table_schema \|\| '.' \|\| table_name) DESC;
	postgres=# \q		postgres=# \q
	</pre>		</pre>

	==~~= Show all tables sizes =~~==		== List table columns ==

	<pre>		<pre>
	root@host:~# su - postgres		root@host:~# su - postgres
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres -d name_db
	postgres=# SELECT ~~table_catalog AS database~~, ~~table_schema \|\| '.' \|\| table_name AS name~~, ~~pg_size_pretty(pg_total_relation_size(table_schema \|\| '.' \|\| table_name)) AS size~~ FROM information_schema.~~tables ORDER BY pg_total_relation_size(table_schema \|\|~~ '.' ~~\|\| table_name) DESC~~;		postgres=# SELECT column_name, is_nullable, data_type, character_maximum_length, numeric_precision FROM information_schema.columns WHERE table_name ='db_table'; # or \d db_table
	postgres=# \q		postgres=# \q
	</pre>		</pre>

T1t0: /* Backup and restore */

2023-11-24T16:37:44Z

Backup and restore

← Older revision		Revision as of 16:37, 24 November 2023
Line 429:		Line 429:
	</pre>		</pre>

			=== Just the database users/roles ===
			'''on source host'''
			<pre>
			postgres@host_src:~$ pg_dumpall -h localhost -U postgres name_db --globals-only --clean --if-exists > dumpall_globalsonly.sql
			</pre>

			note: it contains the postgres user permission too, you can avoid changes to such user removing the related lines in the dump:
			<pre>
			sed -i '/^DROP ROLE.postgres/d;/^CREATE ROLE.postgres/d;/^ALTER ROLE.*postgres/d' dumpall_globalsonly.sql
			</pre>

			'''on destination host'''
			<pre>
			postgres@host_dst:~$ psql -E --set ON_ERROR_STOP=on -h localhost -U postgres -d postgres -f dumpall_globalsonly.sql
			</pre>

T1t0: /* Create database */

2023-09-29T07:58:50Z

Create database

← Older revision		Revision as of 07:58, 29 September 2023
Line 274:		Line 274:
	postgres=# CREATE DATABASE name_db WITH ENCODING 'UTF8' OWNER "db_user" TEMPLATE template0;		postgres=# CREATE DATABASE name_db WITH ENCODING 'UTF8' OWNER "db_user" TEMPLATE template0;
	</pre>		</pre>

			NOTE: the default template used to create a new database is template1, which can contain customized data admins want to be added in any new database. But if you dump a db created from template1 and restore in a new db created from template1, the customization can be done twice leading to conflicts, so the empty db to use as a destination for the restore should be created from template0. Source https://www.postgresql.org/docs/current/manage-ag-templatedbs.html .

	== Test user ==		== Test user ==

T1t0: /* Notes */

2023-07-13T07:44:39Z

Notes

← Older revision		Revision as of 07:44, 13 July 2023
Line 12:		Line 12:
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres=# -- DO YOUR QUERIES		postgres=# -- DO YOUR QUERIES
	postgres=# ~~exit~~		postgres=# \q
	</pre>		</pre>

T1t0: /* Create readonly user */

2023-07-13T07:44:21Z

Create readonly user

← Older revision		Revision as of 07:44, 13 July 2023
Line 259:		Line 259:
	postgres=# CREATE ROLE readonly_user LOGIN PASSWORD XXX;		postgres=# CREATE ROLE readonly_user LOGIN PASSWORD XXX;
	postgres=# GRANT CONNECT ON DATABASE database_to_query TO readonly_user;		postgres=# GRANT CONNECT ON DATABASE database_to_query TO readonly_user;
	postgres=# ~~quit~~		postgres=# \q
	postgres@host:~$ psql -U postgres postgres database_to_query		postgres@host:~$ psql -U postgres postgres database_to_query
	database_to_query=# GRANT USAGE ON SCHEMA public TO readonly_user;		database_to_query=# GRANT USAGE ON SCHEMA public TO readonly_user;
	database_to_query=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;		database_to_query=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
	database_to_query=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;		database_to_query=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
	postgres=# quit=# ~~quit~~		postgres=# quit=# \q
	</pre>		</pre>