This is the third article of a series featuring a collection of useful tasks for administering PostgreSQL version 11 on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I show how to create databases using both SQL statements along with the createdb utility program.
Databases can be created in PostgreSQL using traditional SQL as in the following set of commands which issues a CREATE DATABASE statement in the psql shell to define a database named test.
$ psql psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1)) Type "help" for help. postgres=# CREATE DATABASE test; CREATE DATABASE postgres=# \q
PostgreSQL also provides a utility program, createdb, which can be used to create databases. To accomplish this you issue the createdb program name followed by the name of the database you are creating. Additionally, I prefer to add the --echo flag to the command which prints out the underlying SQL statements that are executed against the PostgreSQL cluster.
$ createdb test2 --echo SELECT pg_catalog.set_config('search_path', '', false) CREATE DATABASE test2;
Notice that this program is used from the standard Linux shell no the psql interactive PostgreSQL shell.
Now when I list the databases in the psql shell I see the new test and test2 datatabases.
$ psql psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1)) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+---------+----------------------- postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | C.UTF-8 | C.UTF-8 | test2 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | (5 rows)
In this article I demonstrated two methods for creating database within a PostgreSQL cluster. Specifically, I gave examples of creating databases using traditional SQL statements plus using the createdb PostgreSQL utiltity program.