How to Alter Databases in PostgreSQL

This PG Casts episode is sponsored by Hashrocket, a consultancy specializing in PostgreSQL; learn more at https://hashrocket.com. To see more PG Casts videos, visit our YouTube channel or https://www.pgcasts.com

Transcript:

In this episode, we're going to explore how to alter databases in Postgres.

First, let's create a new database. We'll call it "example".

```sql
create database example;
```

To make changes to our new database, we can use the "alter database" command. This command has a number of options for making changes to our databases.

#### RENAME TO

To rename a database, we use "alter database" followed by the database name we're wanting to change, and then the "rename to" option. This option is then followed by the new name for our database.

```sql
alter database example rename to test;
```

Use the list metacommand, we can see that example has now been renamed to test.

#### ALLOW_CONNECTIONS and CONNECTION LIMIT

Another thing we can do with alter database is change our connection settings. First, let's try to remove connection privileges to our test database.

To do this, we use "alter database" with our database name, followed by "allow_connections false".

```sql
alter database test allow_connections false;
```

To see that our database is no longer accepting connections, we can try connecting to it using the connect metacommand.

```
\connect test
```

To allow connections, but set a limit to the number of connections for our database, we use alter database again, this time with two options: allow connections, and connection limit.

```sql
alter database test allow_connections true connection limit 1;
```

We can use the connect metacommand again to see that connections have been reenabled for our database.

```
\connect test
```

We can also check that our connection limit was updated by looking at the pg_database table.

```sql
select datname, datconnlimit from pg_database;
```

#### IS TEMPLATE

In the same manner as connections, we can also manage whether or not our database is a template with alter database. This time, we use the "is_template" option.

```sql
alter database test is_template true;
```

We can then check that our database has been updated by querying the pg_database table again.

```sql
select datname, datistemplate from pg_database;
```

#### OWNER TO

Finally, let's use the alter database command to transfer ownership of our database.

We can see with the list metacommand that the current owner of our test database is my user.

```
\l test
```

To transfer ownership to another user, we pass the "owner to" option to alter database, following up with the name of the user we're transferring to. For this example, let's transfer test to postgres.

```sql
alter database test owner to postgres;
```

By using the list metacommand again, we can see that our database owner has now been changed.

```
\l test
```

Thanks for watching!