Reset postgresql

Question

I want to bring postgresql back to its original empty state.

On Ubuntu:

$ sudo service postgresql stop
$ sudo apt-get purge postgresql*

Followed by

$ sudo apt-get install postgresql

Does what I want, but is there any less brutal way of doing the same thing?

What I actually want to do is to check that I can re-create my database from scratch.

Possible duplicate of http://serverfault.com/q/574474/69930. — halfer, May 29 '15 at 17:35

score 7 · Accepted Answer · answered Feb 09 '11 at 15:48

7

Use initdb, creates a new PostgreSQL database cluster. That includes postgresql.conf and pg_hba.conf as well.

answered Feb 09 '11 at 15:48

Frank Heikens

1,258
6
8

Frank is right, that's the postgres way to reset the system. +1 – lynxman Feb 09 '11 at 16:34
This looks good, but the command doesn't seem to exist in ubuntu! – John Lawrence Aspden Feb 09 '11 at 19:54
1

It's under /usr/lib/postgresql/x.y/bin/. – Peter Eisentraut Feb 09 '11 at 21:48

score 3 · Answer 2 · answered Feb 09 '11 at 12:02

First, I have to guess what you mean by "reset"!

If you mean the data, you can just drop all databases using the known SQL statement drop database.

If you mean the configuration, I think it is a good way to remove and re-install it with a fresh config files unless you have an old backup.

score 1 · Answer 3 · answered Feb 09 '11 at 12:53

Like Khaled said, you could just issue drop database, drop user, etc. Statements to clean up all changes made to the initial Database.

For the Database Configuration you might be interested in just putting the files under version control (git,svn &co).

For Subversion you could do something like this:

Install Subversion

$ sudo apt-get install subversion

Setup the Repository

$ mkdir ~/pgsqlsvn
$ svnadmin create

Stop the Service

$ sudo service postgresql stop

Initially fill the repository:

$ cd /etc/postgresql
$ sudo svn co file:///home/you/pgsqlsvn ./
$ sudo svn add * 
$ sudo svn commit

Reverting the Configuration Afterwards

Everytime you need to reset to the initital Database configuration you can just

$ sudo svn update /etc/postgresql -r 1

score 1 · Answer 4 · answered May 29 '15 at 17:27

To expand on Frank's answer, I have done the following to reset PostgreSQL on Ubuntu 14.04 LTS:

sudo service postgresql stop
sudo bash # Root permissions to move the data folder
mv /var/lib/postgresql/9.3/main ~ # Move the database for safety
logout
sudo -u postgres bash
mkdir /var/lib/postgresql/9.3/main
/usr/lib/postgresql/9.3/bin/initdb -D /var/lib/postgresql/9.3/main
sudo service postgresql start

Note that initdb is not in the system path, and if you try to run it, Ubuntu will suggest you need to install an extension package. However, if you have the server itself installed, the command is still available - just hidden.

I expect this can be modified for other versions of PostgreSQL - just tinker a bit with the version number in the paths.

I could probably have shortened this process a bit by logging on as postgres for both moving the old database and creating the new one. Note that initdb will refuse to run as root, so running as postgres makes sense.

(A reset of this kind is useful for folks coming to this database platform from other systems, such as MySQL. Once you've added roles, users, grants, default privs etc, it is hard to remember what command had what effect. Wiping and trying again removes all doubt).