So far, we have seen how to install PostgreSQL, initialize a PostgreSQL cluster, and start and stop a cluster. In this recipe, we shall discuss how to create or drop a database using the createdb and dropdb utilities.
There are multiple ways to create and drop a database in PostgreSQL. Upon successful installation of PostgreSQL, you have two utilities, called createdb and dropdb, that can be used to create and drop databases in PostgreSQL. The same can also be done using the psql utility.
Let's look at this in detail in these recipes.
Getting ready
In order to create or drop databases, we must either be a superuser or have the role CREATEDB. Also, the user who is dropping the database should either be a superuser or the OWNER of the database.
How to do it...
The following are the steps involved in creating and dropping a database using the createdb and dropdb utilities:
- We will use help to list all the arguments for the createdb utility:
$ createdb --help
createdb creates a PostgreSQL database.
Usage:
createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
-D, --tablespace=TABLESPACE default tablespace for the database
-e, --echo show the commands being sent to the server
-E, --encoding=ENCODING encoding for the database
-l, --locale=LOCALE locale settings for the database
--lc-collate=LOCALE LC_COLLATE setting for the database
--lc-ctype=LOCALE LC_CTYPE setting for the database
-O, --owner=OWNER database user to own the new database
-T, --template=TEMPLATE template database to copy
-V, --version output version information, then exit
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
By default, a database with the same name as the current user is created.Report bugs to <pgsql-bugs@postgresql.org>.
- We will run the following command to create a database using createdb:
$ createdb -e pgtest -O user1
SELECT pg_catalog.set_config('search_path', '', false)
CREATE DATABASE pgtest OWNER user1;
- We will then create a database using a template as follows:
$ createdb -e pgtest -O user1 -T percona
SELECT pg_catalog.set_config('search_path', '', false)
CREATE DATABASE pgtest OWNER user1 TEMPLATE pg11;
- We will use help to list all the arguments for the dropdb utility:
$ dropdb --help
dropdb removes a PostgreSQL database.
Usage:
dropdb [OPTION]... DBNAME
Options:
-e, --echo show the commands being sent to the server
-i, --interactive prompt before deleting anything
-V, --version output version information, then exit
--if-exists don't report error if database doesn't exist
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
Report bugs to <pgsql-bugs@postgresql.org>.
- We will now drop a database using dropdb:
$ dropdb -i pgtest
Database "pgtest" will be permanently removed.
Are you sure? (y/n) y
How it works
The best way to understand the options that can be passed to any utility is through --help. As seen in Step 1 and Step 4, we could list all the possible arguments we could pass to the createdb and dropdb utilities.
As seen in the options available with createdb in Step 1, we can use -e to print the commands sent to the server and -O to assign the ownership of the database to a user. Using -e does not stop it from running createdb but just prints the commands executed through createdb. As seen in Step 2, using -e and -O has created the database.
Another option available in PostgreSQL is creating a database using a template. Sometimes, we may create a template and wish to apply the same template to future databases. So, everything maintained inside the template database specified is copied to the database being created. As seen in step 3, we are creating a database named pgtest using a template database: percona.
When you wish to drop a database you have created, you could use the command seen in Step 5. It uses the dropdb utility to drop the database.
When you create a database or drop a database using any of the aforementioned utilities, you could simply use psql to list the databases you have created. We could either use the psql shortcut discussed in the previous recipe or query the catalog table: pg_database.
$ psql -c "\l"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
pgtest | user1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
$ psql -c "select oid, datname from pg_database"
oid | datname
-------+-----------
13881 | postgres
16385 | pgtest
1 | template1
13880 | template0
(4 rows)
There's more
We have seen how to create and drop a database using the createdb and dropdb utilities. The same can also be achieved using psql through CREATE and DROP commands. It is of course very simple to run a simple CREATE DATABASE or DROP DATABASE command. But, when you need to combine that with several parameters such as owner, encoding, tablespace, and connection limit, you may need to find the correct syntax. In order to do that, you could use \help as seen in the following example:
$ psql
psql (13.1)
Type "help" for help.
postgres=# \help CREATE DATABASE
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]
postgres=# \help DROP DATABASE
Command: DROP DATABASE
Description: remove a database
Syntax:
DROP DATABASE [ IF EXISTS ] name
So, the command to create a database that is owned by user2 with a connection limit of 200 should be as follows:
postgres=# CREATE DATABASE mydb WITH OWNER user2 CONNECTION LIMIT 200;
CREATE DATABASE
postgres=# \l+ mydb
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------+-------+----------+-------------+-------------+-------------------+---------+------------+-------------
mydb | user2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7809 kB | pg_default |
(1 row)
Similarly, the command to drop a database, mydb, is as follows:
postgres=# DROP DATABASE mydb ;
DROP DATABASE
postgres=# \l+ mydb
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------+-------+----------+---------+-------+-------------------+------+------------+-------------
(0 rows)
