PG Casts

Altering Databases

Episode #26 hosted by Mary Lee

Hey everyone, today we're going to look at how to alter databases in Postgres.

Postgres Version 11

Transcript

First, let’s create a new database. We’ll call it “example”.

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.

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”.

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.

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.

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.

alter database test is_template true;

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

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.

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!

Stay up-to-date with PG Casts news!