NickPGSmith: /* Upgrades */

2025-05-12T05:41:42Z

Upgrades

← Older revision		Revision as of 05:41, 12 May 2025
Line 50:		Line 50:
	remove old database:		remove old database:
	rm -rf /var/lib/pgsql/data-old		rm -rf /var/lib/pgsql/data-old

			Update collation version:
			reindex database wiki;
			alter database wiki REFRESH COLLATION VERSION;

	=== psql ===		=== psql ===

NickPGSmith: 3 revisions imported

2024-05-08T04:53:36Z

3 revisions imported

← Older revision	Revision as of 04:53, 8 May 2024
(No difference)

NickPGSmith: /* Upgrades */

2023-05-08T20:12:16Z

Upgrades

← Older revision		Revision as of 20:12, 8 May 2023
Line 44:		Line 44:

	Also, reindex.with [https://www.postgresql.org/docs/12/app-reindexdb.html reindexdb] or [https://www.postgresql.org/docs/12/sql-reindex.html REINDEX].		Also, reindex.with [https://www.postgresql.org/docs/12/app-reindexdb.html reindexdb] or [https://www.postgresql.org/docs/12/sql-reindex.html REINDEX].

			reindex -h localhost -U postgres
			vacuumdb h localhost -U postgres --all --analyze-in-stages

			remove old database:
			rm -rf /var/lib/pgsql/data-old

	=== psql ===		=== psql ===

NickPGSmith: 1 revision imported

2023-02-17T21:02:12Z

1 revision imported

← Older revision	Revision as of 21:02, 17 February 2023
(No difference)

NickPGSmith at 08:56, 26 August 2022

2022-08-26T08:56:10Z

New page

== MySQL ==

After install: mysqladmin -u root password 'newpassword'
* /etc/my.cnf

== Postgres ==

See also [https://fedoraproject.org/wiki/PostgreSQL here].

=== Initialisation ===

Install Packages:
* postgresql-server postgresql-libs postgresql postgresql-contribsystem php-pgsql postgresql-devel php-pecl-apcu
Also for python:
* python-devel redhat-rpm-config python3-postgresql

postgresql-setup --initdb
firewall-cmd --permanent --add-port=5432/tcp

Suggested changes to postgresql.conf:
listen_addresses = '*'
password_encryption = scram-sha-256
shared_buffers = 256MB
work_mem = 64MB
effective_cache_size = 4GB # Not allocation: guide to OS cache size

Temporary setting in pg_hba.conf to allow local postgres connection:
local all all trust

su - postgres
psql

then change postgres user's password. These entries set password-based authentication:
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
host all all 192.168.1.0/24 scram-sha-256
host everywheredb all 0.0.0.0/0 scram-sha-256
host everywheredb all ::0/0 scram-sha-256

=== Upgrades ===

After major upgrades (requires package postgresql-upgrade):
postgresql-setup --upgrade

Also, reindex.with [https://www.postgresql.org/docs/12/app-reindexdb.html reindexdb] or [https://www.postgresql.org/docs/12/sql-reindex.html REINDEX].

=== psql ===

psql -h localhost -U someuser dbname

See also [https://www.postgresql.org/docs/current/static/app-psql.html here] for meta-commands:

{| class="wikitable"

!Meta Command
!Description
|-
|\list [pattern]
|List databases
|-
|\connect somedatabase
|Connect to somedatabase
|-
|\d[+] [pattern]
|Show all tables, views, sequences, etc
|-
|\d+ [tablename]
|Describe the table
|-
|\l+ [pattern]
|Show all databases
|-
|\dn
|Show all schemas
|-
|\dt
|Show details of tables
|-
|\du[+] [pattern]
|Show all users (roles)
|-
|\i /somepath/somefile.sql
|Execute commands from file
|-
|\password
|Change password
|-

|}

Dump data from a query to CSV:
\copy (select * from sometable) to '/tmp/somefile.csv' with csv delimiter ',';

* "public" schema always exists.
* show search_path;
* Template1 copied when a new database is created

=== Useful SQL ===

Create user (aka "role"):
CREATE USER jsmith WITH nocreatedb nocreaterole nosuperuser PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE "dbname" TO jsmith;

Change password:
ALTER USER jsmith WITH PASSWORD 'secret';

Add privileges:
GRANT USAGE ON SCHEMA sales to jsmith;
GRANT CREATE ON SCHEMA sales to jsmith;

Show shadow table:
SELECT * FROM PG_SHADOW;

Drop all tables in a schema:
SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;'
FROM pg_tables
WHERE schemaname = 'public';

Change owner of table:
ALTER TABLE mathoid OWNER TO WIKI;

=== Backup and Restore ===

pg_dump -h localhost -p 5432 -d DATABASE -U postgres -f /tmp/db.dmp
psql -h localhost -p 5432 -d DATABASE -U postgres < /tmp/db.dmp

=== Admin Console ===

Use the [https://www.pgadmin.org/download/windows.php PGAdmin] Console.

Linux - Databases - Revision history

NickPGSmith: /* Upgrades */

NickPGSmith: 3 revisions imported

NickPGSmith: /* Upgrades */

NickPGSmith: 1 revision imported

NickPGSmith at 08:56, 26 August 2022