{"id":2349,"date":"2025-03-09T16:42:18","date_gmt":"2025-03-09T09:42:18","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2349"},"modified":"2025-03-12T20:04:19","modified_gmt":"2025-03-12T13:04:19","slug":"postgresql-alter-database","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-database-administration\/postgresql-alter-database\/","title":{"rendered":"PostgreSQL ALTER DATABASE Statement"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn how to use the PostgreSQL ALTER DATABASE<\/code> statement to modify the properties of an existing database.<\/p>\n\n\n\n

PostgreSQL ALTER DATABASE Statement Overview #<\/a><\/h2>\n\n\n\n

To change the properties of an existing PostgreSQL database, you use the ALTER DATABASE<\/code> statement:<\/p>\n\n\n

ALTER<\/span> DATABASE<\/span> database_name\n[WITH<\/span>] option<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following table displays the available options:<\/p>\n\n\n\n
Option<\/th>Description<\/th><\/tr><\/thead>
RENAME TO new_name<\/code><\/td>Renames the database<\/td><\/tr>
OWNER TO new_owner<\/code><\/td>Changes the database’s owner<\/a> to a new one<\/td><\/tr>
SET PARAMETER<\/code><\/td>Modifies a runtime configuration parameter<\/td><\/tr>
RESET PARAMETER<\/code><\/td>Resets a runtime configuration parameter<\/td><\/tr>
CONNECTION LIMIT<\/code><\/td>Sets the maximum number of concurrent connections<\/td><\/tr>
TABLESPACE tablespace_name<\/code><\/td>Moves the database to a new tablespace<\/a>.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The ALTER DATABASE<\/code> statement can be helpful in changing database settings, ownership, connection limits, and tablespace assignments.<\/p>\n\n\n\n
Note that only a superuser<\/a> or the database owner<\/a> has the privilege to execute an\u00a0ALTER DATABASE<\/code>\u00a0statement.<\/p>\n\n\n\n
We’ll create a new database<\/a> to demonstrate the ALTER DATABASE<\/code> statement:<\/p>\n\n\n\n
First, connect to your PostgreSQL server using psql<\/code>:<\/p>\n\n\n
psql -U postgres<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new database called db<\/code>:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> db;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Renaming a Database #<\/a><\/h3>\n\n\n\n
The following statement renames the database<\/a> db<\/code> to mydb<\/code>:<\/p>\n\n\n
ALTER<\/span> DATABASE<\/span> db\nRENAME<\/span> TO<\/span> mydb;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you cannot rename a database while connected to it. Additionally, the database you want to rename must have no active connections.<\/p>\n\n\n\n
Changing the Owner #<\/a><\/h3>\n\n\n\n
First, create a new role john<\/code> with the LOGIN<\/code> privilege:<\/p>\n\n\n
CREATE<\/span> ROLE<\/span> john\nWITH<\/span> LOGIN<\/span> PASSWORD<\/span> 'securepassword'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, change the ownership of the mydb<\/code> to john<\/code>:<\/p>\n\n\n
ALTER<\/span> DATABASE<\/span> mydb\nOWNER<\/span> TO<\/span> john;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Only the owner of the mydb<\/code> database or a superuser can execute this command. After this, john<\/code> is the new owner of the mydb<\/code>. It means that john<\/code> has all privileges on the mydb<\/code> database.<\/p>\n\n\n\n
Setting a Configuration Parameter #<\/a><\/h3>\n\n\n\n
The following statement changes the work_mem<\/code> setting for mydb<\/code>:<\/p>\n\n\n
ALTER<\/span> DATABASE<\/span> mydb\nSET<\/span> work_mem TO<\/span> '64MB'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
This new work_mem<\/code> applies to all new connections to the mydb<\/code> database.<\/p>\n\n\n\n
Resetting a Configuration Parameter #<\/a><\/h3>\n\n\n\n
The following statement resets work_mem<\/code> to its default value:<\/p>\n\n\n
ALTER<\/span> DATABASE<\/span> mydb\nRESET<\/span> work_mem;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Limiting the Number of Connections #<\/a><\/h3>\n\n\n\n
The following statement sets the number of concurrent connections to 50:<\/p>\n\n\n
ALTER<\/span> DATABASE<\/span> mydb\nWITH<\/span> CONNECTION<\/span> LIMIT<\/span> 50<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Moving a Database to a New Tablespace #<\/a><\/h3>\n\n\n\n
The following statement creates a new tablespace and moves the mydb<\/code> to the new tablespace:<\/p>\n\n\n\n
First, create a new tablespace<\/a>:<\/p>\n\n\n
CREATE<\/span> TABLESPACE<\/span> mydb_ts\nLOCATION<\/span> 'C:\\pgdata\\primary'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Replace the path to the tablespace with your path.<\/p>\n\n\n\n
Second, change the tablespace of the mydb<\/code> database:<\/p>\n\n\n
ALTER<\/span> DATABASE<\/span> mydb\nSET<\/span> TABLESPACE<\/span> mydb_ts;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the ALTER DATABASE<\/code> statement to modify the properties of an existing database.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t