Come modificare, rinominare, eliminare e copiare database in PostgreSQL

Questo post esamina alcuni dei comandi disponibili per gestire i database PostgreSQL dal prompt PSQL. Rivedremo i metodi per modificare, rinominare, eliminare e copiare un database.

Riassumiamo alcuni comandi PostgreSQL di base:

1. Sintassi per la creazione di database:

CREATE DATABASE name user_name ] template ] encoding ] lc_collate ] lc_ctype ] tablespace ] connlimit ] ]

2. Per ottenere una lista di database nel cluster attuale:

a. \l+

b. select * from pg_database ;

Dichiarazioni ALTER database

Le dichiarazioni ALTER database possono essere usate per cambiare gli attributi esistenti del database di destinazione.

Modificare il proprietario del database

Per modificare il proprietario del database, l’utente collegato deve essere il proprietario del database ed essere anche un membro diretto o indiretto del nuovo ruolo proprietario, e l’utente collegato deve avere il privilegio CREATEDB.

Sintassi

ALTER DATABASE <database_name> OWNER TO <new_owner>;

Esempio

ALTER DATABASE test OWNER TO user;

Impostazione del database template

Di default, abbiamo solo due database template presenti in un cluster-template0 e template1. Altri database non possono essere usati come template per impostazione predefinita. Nel caso tu abbia bisogno di creare un modello di database personalizzato per il tuo ambiente, puoi usare questa opzione.

Sintassi

ALTER DATABASE <database_name> WITH IS_TEMPLATE = true;

Esempio

ALTER DATABASE test WITH IS_TEMPLATE = true;

Restrizione di nuove connessioni per un database specifico

Questa opzione è molto utile per mantenere il database. Puoi limitare le nuove connessioni a un database specifico durante la finestra di manutenzione del database. Per abilitare questa opzione, nessun utente è connesso al database di destinazione.

Sintassi

ALTER DATABASE <database_name> WITH ALLOW_CONNECTIONS = false;

Esempio

ALTER DATABASE test2 WITH ALLOW_CONNECTIONS = false;

Restrizione del limite di connessione per un database specifico.

Per sicurezza e prestazioni ottimali, puoi limitare il numero di connessioni che possono connettersi al database di destinazione. Per impostazione predefinita, il valore di questo parametro è illimitato, cioè -1, per ogni database.

Sintassi

ALTER DATABASE <database_name> WITH CONNECTION LIMIT <max_number_of_connections>;

Esempio

ALTER DATABASE test2 WITH CONNECTION LIMIT 10;

Cambiare il tablespace predefinito per il database

Puoi spostare fisicamente gli oggetti del tablespace predefinito come tabelle e indici dal tablespace predefinito a un nuovo tablespace personalizzato. Il nuovo tablespace di default deve essere vuoto per questo database, e nessuno può essere connesso al database durante questa attività.

Nota: Il proprietario del database o un superutente deve fare questa modifica, e l’utente deve avere i privilegi CREATE per il nuovo tablespace.

Sintassi

ALTER DATABASE <database_name> SET TABLESPACE <custom_tablespace_name>;

Esempio

ALTER DATABASE test SET TABLESPACE custom_tblspc;

Dichiarazioni di database RENAME

Si può cambiare il nome del database corrente usando RENAME. Tuttavia, non puoi rinominare il database mentre è accessibile da altre connessioni.

Nota: Il proprietario del database o il superutente deve fare questa modifica, e l’utente deve avere i privilegi di CREATEDB.

Sintassi

ALTER DATABASE <old_database_name> RENAME TO <new_database_name>;

Esempio

ALTER DATABASE test RENAME TO test123;

DROP dichiarazioni del database

Puoi eliminare il database di destinazione. Questo rimuove le voci di catalogo per il database e cancella la directory contenente i dati. Può essere eseguito solo dal proprietario del database. Inoltre, non può essere eseguito mentre tu o chiunque altro è connesso al database di destinazione.

Nota: DROP DATABASE non può essere annullato.

Sintassi

DROP DATABASE <database_name>;

Esempio

DROP DATABASE test2;