LinuxCommandLibrary

dropdb

Remove a PostgreSQL database

TLDR

Destroy a database

$ dropdb [dbname]
copy

Request a verification prompt before any destructive actions
$ dropdb [[-i|--interactive]] [database_name]
copy

Connect with a specific username and destroy a database
$ dropdb [[-U|--username]] [username] [dbname]
copy

Force a password prompt before connecting to the database
$ dropdb [[-W|--password]] [dbname]
copy

Suppress a password prompt before connecting to the database
$ dropdb [[-w|--no-password]] [database_name]
copy

Specify the server host name
$ dropdb [[-h|--host]] [host] [database_name]
copy

Specify the server port
$ dropdb [[-p|--port]] [port] [database_name]
copy

Attempt to terminate existing connections before destroying the database
$ dropdb [[-f|--force]] [database_name]
copy

SYNOPSIS

dropdb [connection-option...] [option...] dbname

PARAMETERS

dbname
    The name of the database to be dropped.

-h host, --host=host
    Specifies the host name of the machine on which the server is running.

-p port, --port=port
    Specifies the TCP port or local Unix domain socket file extension the server is listening on.

-U username, --username=username
    User name to connect as.

-W, --password
    Forces dropdb to prompt for a password before connecting to a database.

-e, --echo
    Echoes the SQL commands that dropdb generates and sends to the server.

-i, --interactive
    Prompts for confirmation before actually destroying the database.

-q, --quiet
    Do not display a response. This option suppresses success messages.

--if-exists
    Do not throw an error if the database does not exist. A notice is issued in this case.

--force
    Force connections to the target database to be terminated. This option ensures the drop succeeds even if active sessions exist.

--dry-run
    Shows the commands that dropdb would execute, but does not actually run them.

DESCRIPTION

dropdb is a utility for removing existing PostgreSQL databases. It is a wrapper around the SQL command DROP DATABASE.

When executed, dropdb connects to the PostgreSQL server (typically the 'postgres' database by default, unless specified) and issues the DROP DATABASE command for the specified database name. This action permanently deletes all data, tables, indexes, functions, and other objects within that database.

Due to its destructive nature, dropdb requires appropriate superuser or database owner privileges on the PostgreSQL server. It's crucial to ensure that no active connections exist to the target database during the drop operation, as PostgreSQL will prevent the drop if there are active sessions, unless the --force option is used. It's an essential administrative tool for database lifecycle management, allowing administrators to clean up old or unnecessary databases.

CAVEATS

dropdb is a highly destructive command.

Irreversible Data Loss: Dropping a database permanently deletes all its data and objects. There is no undo mechanism.

Permissions: You must have superuser privileges or be the owner of the database to drop it.

Active Connections: By default, you cannot drop a database if there are active connections to it. Use the --force option with extreme caution to terminate connections, as this can disrupt other users.

System Databases: Do not attempt to drop template0 or template1 unless you know exactly what you are doing, as these are critical for PostgreSQL's operation.

CONNECTION DATABASE

dropdb connects to the 'postgres' database by default when dropping another database. If the 'postgres' database does not exist, it connects to 'template1'.

ENVIRONMENTAL VARIABLES

dropdb uses the environment variables PGHOST, PGPORT, PGUSER, PGPASSWORD, and PGDATABASE for default connection parameters. If PGPASSWORD is set, dropdb will use it, potentially overriding -W.

SECURITY

Always be mindful of the database name when using dropdb. Double-check before executing, especially in production environments. Using -i for interactive confirmation or --dry-run for testing is highly recommended.

HISTORY

dropdb has been a fundamental utility in the PostgreSQL ecosystem since its early versions. It provides a simple command-line interface for performing the DROP DATABASE SQL operation, simplifying database administration tasks by wrapping complex SQL commands in a user-friendly tool. Its design reflects PostgreSQL's commitment to robust and accessible database management.

SEE ALSO

createdb(1), psql(1), pg_dump(1), pg_restore(1)

Copied to clipboard