How to clear a Postgres database without logging in

Posted on 06-12-2013 14:54 by graham
It might sometimes happen that your Postgres database is too big and you just want to remove all data from it without reinstalling Postgres. If you are able to log in to Postgres console and drop all databases (or drop them using dropdb) without logging in, it's all good. But what if your Postgres server is down and cannot even be started, e.g. because of too little disk space.

The only solution might be to clear all data. The way to do this is to remove all contents of the Postgres data directory:
rm -rf /var/lib/pgsql/9.2/data/*

Then you need to reinitialize the data directory using the initdb command:
initdb --encoding=UTF8 /var/lib/pgsql/9.2/data/

Then you should be able to start your Postgres server.

However, you might still sometimes encounter an error that says:
psql: FATAL:  could not open relation mapping file "global/pg_filenode.map": No such file or directory

In this case there may be two reasons: either you have not set privileges to the data directory for the postgres user (make them the owner of the data directory), or you have an old/invalid PGDATA used somewhere that points to an invalid data directory.
Comments

 

Add comment

Has this tutorial been helpful to you? Or do you see anything wrong? We appreciate your opinion!
Your comment:
Show formatting hints
HTML is disallowed, but in your text you can use the following markup
  • [code][/code] for a block of code
  • [tt][/tt] for inline code
  • [link]link href|link anchor[/link] for links
  • [b][/b] for bold text
Email:
+ Ask a question
If you have a technical question related to programming and computers, ask it here. Other users will help you solve it!
Unanswered questions
Share your knowledge by helping others solve their problems