Completely reset PostgreSQL to default?

Question

Question

Is there a way to completely reset a PostgreSQL installation on Linux, so it is in the same state as when I installed it?

Idea

I have considered

rm -rf /var/lib/pgsql/*
rm -rf /var/lib/pgsql/backups/*
rm -rf /var/lib/pgsql/data/*

but perhaps that is not a recommended method.

Purpose

This would be handy to get rid of left overs from previous programs that have used it.

Solution 1

That should do it - just make sure first that the current configuration actually does store the files in those directories.

Once it's done, re-initiate the database:

sudo -U pgsql initdb

Solution 2

It all depends on how it was installed.

Ubuntu, from packages (built-in or apt.postgresql.org)

Use pg_wrapper. See the Ubuntu docs for PostgreSQL. You want to pg_dropcluster all existing Pg clusters, then pg_createcluster a clean new one.

Do not just delete the data dir and re-initdb.

CentOS/RH/Fedora, built-in packages

I don't have this installed and can't easily test right now. From memory I think it's safe to just stop the server and delete the data dir.

CentOS/RH/Fedora, yum.postgresql.org packages

See /usr/share/doc/postgresql??-?.?.?/README.rpm-dist.

Stop the server with systemctl or the service wrapper command, delete the data dir, then run /usr/pgsql-9.3/bin/postgresql93-setup initdb (adjusting paths as appropriate for your version).

Any distro, from EDB installer

Stop the server, delete the data dir and initdb a new cluster. See the installer docs.

From source

Stop server, delete data dir, re-initdb.

OS X

Homebrew: brew uninstall postgresql; brew cleanup; brew install postgresql

Postgres.app? EDB installer? MacPorts?

Find a beer. Drink the beer. Repeat. If this doesn't make the pain go away, get something stronger.

Windows

• Stop service
• Delete data dir
• re-run initdb using runas.exe as the postgres user (pre-9.2) or NETWORKSERVICE (9.2+). Or just change the ownership afterwards.

Solution 3

Isn't it best to just remove and reinstall?

That way you'll get the latest version and update any dependences along the way.

Run: sudo apt-get --purge remove postgresql

Then: sudo apt-get install postgresql

Comments

[halfer]

Possible duplicate of serverfault.com/q/233234/69930.

[John Mee]

Drop the cluster... stackoverflow.com/a/2748644/75033

[Craig Ringer]

No, please don't. On Ubuntu at least that'll cause a hideous mess because it expects you to use its own wrapper tools.

[Jenny D]

It does? Well, there's one more reason to use some other system, then... Personally I'd vote for FreeBSD, but each to their own.

[Craig Ringer]

@halfer To start with postgresql.conf and pg_hba.conf wont won't be where the ububtu scripts expect. Or they will ignore the ones initdb created and keep using the old ones.

[Sebastian Wagner]

This did not remove existing DB + users (as of Postgres 9.5)

[Curtis Yallop]

For OS X, brew uninstall and cleanup does not touch my data dir /usr/local/var/[email protected]. (it removes the app in /usr/local/opt/[email protected], note the "opt" not "var") "rm -rf /usr/local/var/[email protected]" was needed before reinstalling.

[fIwJlxSzApHEZIl]

what if this is a remote database that we've connected to and not one that we've initialized locally?

[Jenny D]

@anon58192932 If you don't have access to that server, you can't reset it to the state it was in when it was fresh installed. There are files that may have been edited on that server, which you can't affect via just an SQL connection.

[Bobort]

I already deleted the database directory before I knew what I was doing. Any advice for us in that circumstance?

[Bobort]

Okay, I had to use sudo to run the pg_dropcluster command. In my case: sudo pg_dropcluster --stop 10 main