Удалить субд postgresql linux

How to delete all databases on Postgres?

I take daily backs of our postgres development box using: pg_dumpall -h 127.0.0.1 -U user -w | gzip blah.gz Since 9.0 is now a release candidate I would like to restore this daily backup on a daily basis to a postgres9.0rc1 box for testing, however I’m not sure how to script it repeatedly. Is there some directory I can nuke to do this?

5 Answers 5

You can do «drop cluster» and «create cluster» which will automtically erase all databases. Erase all data in you $PGDATA directory and reinit the cluster using:

initdb -D /usr/local/pgsql/data 

To be clear, you should replace /usr/local/pgsql/data with whatever your $PGDATA directory is. For example, for PostgreSQL installed via Homebrew, it’s /usr/local/var/postgres . Also, if you export $PGDATA , then you can run initdb with no arguments. Finally, do note that deleting your $PGDATA directory is a separate step. initdb does not do it for you. Oh, and the deletion won’t work right if your postgres server is still running.

$ pg_dropcluster 9.2 main $ pg_createcluster 9.2 main $ pg_ctlcluster 9.2 main start $ pg_restore -f your_dump_file 

where 9.2 = cluster version and main = cluster name

I would recommend using the flag -e ‘UTF-8’ for pg_createcluster otherwise you may have to be changed later if you want UTF-8 databases and the locale is not set to UTF8.

WARNING: this also nukes your postgres config — I literally just wanted a CLEAN database, NOT to also purge the previously optimized config. grumble at least it was the non-production server.

Granted the question is 9 years old at this point, but it’s still the second google result for deleting all databases. If you just want to go from N DBs to 0 without jacking with your config and also having rummage through the file system, this is a much better answer:

From the answer, the following script will generate N drop database commands, one for each non-template DB:

select 'drop database "'||datname||'";' from pg_database where datistemplate=false; 

From there, you can edit and run manually, or pipe further along into a script. Here’s a somewhat verbose one-liner:

echo \pset pager off \copy (select ‘drop database «‘||datname||'»;’ from pg_database where datistemplate=false) to STDOUT; | psql -U -d postgres | | psql -U -d postgres

1. This is a series of pipes
2. echo \pset pager off \copy (select ‘drop database «‘||datname||'»;’ from pg_database where datistemplate=false) to STDOUT; generates a string for psql to execute
   1. \pset pager off ensures you get all records instead of that (54 rows) crap
   2. \copy (select ‘drop database «‘||datname||'»;’ from pg_database where datistemplate=false) to STDOUT; executes the aforementioned query, sending the result to STDOUT. We have to do this since we lead with \pset .
   1. Windows users can use findstr /v «Pager» or findstr /b «drop»
   2. *nix users can use grep ‘drop’

   Источник

   PostgreSQL Drop Database with Examples

   PostgreSQL offers two command-line methods to drop a database — using the DROP DATABASE statement or a shell utility.

   Removing unused databases is good practice and helps keep the workspace clean. However, keep in mind that deleting an existing PostgreSQL database removes all catalog entries and data for that database.

   Continue reading to learn how to drop a database in PostgreSQL.

   

   • PostgreSQL 10 or higher installed and configured (follow our guide for Ubuntu or Windows; if already installed, check the PostgreSQL version on the system).
   • Access to the terminal with sudo privileges.

   DROP DATABASE Statement

   Important: ONLY the database owner can delete a database.

   The first method to remove a PostgreSQL database is to use the following SQL statement:

   The command removes the directory containing the database information and the catalog entries. Only the database owner can execute the DROP DATABASE command. If anyone is currently using the database, the command does not execute.

   To see how DROP DATABASE works, do the following:

   1. Open the terminal (CTRL+ALT+T).

   

   

   The terminal prints the executed statement.

   

   The database from the previous step shows up on the list.

   

   The output shows the executed statement.

   6. List all databases again:

   The example database no longer appears in the list.

   IF Exists

   The IF EXISTS option is open for all versions where DROP DATABASE is available. The full command syntax with the IF EXISTS option is as follows:

   The option first checks if a database exists before deleting it. If a database exists, the command drops the database. However, if a database doesn’t exist, the command prints an informative notice message.

   To test how the command works, follow the steps below:

   1. Create an example database:

   2. Drop the database using the IF EXISTS option:

   DROP DATABASE IF EXISTS example;

   

   The result is identical to using DROP DATABASE if the database does exist.

   3. The database is no longer available. Rerun the command to see the output:

   DROP DATABASE IF EXISTS example;

   

   A notice message prints stating the database does not exist.

   4. To see the difference between using IF EXISTS and omitting the option, run the following command:

   

   Using DROP DATABASE without the IF EXISTS option on a non-existent database throws an error message.

   WITH (FORCE)

   The WITH (FORCE) option is available in PostgreSQL version 13 and higher.

   The DROP DATABASE method won’t remove the database if it’s in use. If the database is in use, the terminal prints an error that a database session is open.

   

   Add the WITH (FORCE) option to forcefully close the session and delete the database:

   

   If possible, Postgres closes the user’s session and deletes the database forcefully.

   The dropdb Utility

   The dropdb shell utility is a wrapper for the DROP DATABASE command. Effectively, the two methods are identical. However, dropdb offers additional options including removing databases remotely.

   Options

   The table below shows all the possible options when using the dropdb utility.

   Option	Type	Description
   -e —echo	Option	Prints the commands that dropdb sends to the server.
   -f —force	Option	Attempts to terminate all current connections before dropping the database.
   -i —interactive	Option	Prompts verification before executing database deletion.
   -V —version	Option	The console prints the utility version.
   —if-exists	Option	Prints a notice instead of an error if the database does not exist.
   -? —help	Option	Show the help menu.
   -h —host=	Connection parameter	Specifies the hostname of the machine where the server is running.
   -p —port=	Connection parameter	Specifies the TCP port where the server is listening.
   -U —username	Connection parameter	Connect as the specified user.
   -w —no-password	Connection parameter	Never issue the password prompt. Useful for batch and script jobs when no user is present.
   -W —password	Connection parameter	Force password prompt. Without the option, the server loses the connection attempt if a password is necessary.
   —maintenance-db=	Connection parameter	The option specifies the database name connection.

   For example, try the following command to see how dropdb works with the -i and -e options:

   The program asks for confirmation before the deletion because of the -i tag.

   

   Press y to confirm. The program prints the commands generated to the server. Because the database is non-existent, the program throws an error and exits.

   After following the examples from this guide, you know how to drop a PostgreSQL database using two methods.

   To learn how to drop a user in multiple ways, read our guide on how to delete Postgres user.

   Next, consider learning about the different data types in PostgreSQL.

   Источник

   Удалить субд postgresql linux

   DROP DATABASE — remove a database

   Synopsis

   DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, . ] ) ] where option can be: FORCE

   Description

   DROP DATABASE drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. It cannot be executed while you are connected to the target database. (Connect to postgres or any other database to issue this command.) Also, if anyone else is connected to the target database, this command will fail unless you use the FORCE option described below.

   DROP DATABASE cannot be undone. Use it with care!

   Parameters

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

   The name of the database to remove.

   Attempt to terminate all existing connections to the target database. It doesn’t terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.

   This will fail if the current user has no permissions to terminate other connections. Required permissions are the same as with pg_terminate_backend , described in Section 9.27.2. This will also fail if we are not able to terminate connections.

   Notes

   DROP DATABASE cannot be executed inside a transaction block.

   This command cannot be executed while connected to the target database. Thus, it might be more convenient to use the program dropdb instead, which is a wrapper around this command.

   Compatibility

   There is no DROP DATABASE statement in the SQL standard.

   See Also

   Submit correction

   If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

   Copyright © 1996-2023 The PostgreSQL Global Development Group

   Источник

   Читайте также:  Одноплатные компьютеры на linux