Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
PostgreSQL 13 Cookbook

You're reading from   PostgreSQL 13 Cookbook Over 120 recipes to build high-performance and fault-tolerant PostgreSQL database solutions

Arrow left icon
Product type Paperback
Published in Feb 2021
Publisher Packt
ISBN-13 9781838648138
Length 344 pages
Edition 1st Edition
Languages
Concepts
Arrow right icon
Author (1):
Arrow left icon
Vallarapu Naga Avinash Kumar Vallarapu Naga Avinash Kumar
Author Profile Icon Vallarapu Naga Avinash Kumar
Vallarapu Naga Avinash Kumar
Arrow right icon
View More author details
Toc

Table of Contents (14) Chapters Close

Preface 1. Cluster Management Fundamentals 2. Cluster Management Techniques FREE CHAPTER 3. Backup and Recovery 4. Advanced Replication Techniques 5. High Availability and Automatic Failover 6. Connection Pooling and Load Balancing 7. Securing through Authentication 8. Logging and Analyzing PostgreSQL Servers 9. Critical Services Monitoring 10. Extensions and Performance Tuning 11. Upgrades and Patches 12. About Packt 13. Other Books You May Enjoy

Creating and dropping databases

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:

  1. 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 <[email protected]>.
  1. 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;
  1. 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;
  1. 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 <[email protected]>.
  1. 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)
You have been reading a chapter from
PostgreSQL 13 Cookbook
Published in: Feb 2021
Publisher: Packt
ISBN-13: 9781838648138
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image