22

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.

Jasmine Lognnes
  • 2,520
  • 8
  • 33
  • 51

3 Answers3

28

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.
the
  • 468
  • 8
  • 23
Craig Ringer
  • 11,083
  • 9
  • 40
  • 61
  • 2
    For OS X, brew uninstall and cleanup does not touch my data dir /usr/local/var/postgresql@9.5. (it removes the app in /usr/local/opt/postgresql@9.5, note the "opt" not "var") "rm -rf /usr/local/var/postgresql@9.5" was needed before reinstalling. – Curtis Yallop Apr 23 '18 at 22:33
  • I already deleted the database directory before I knew what I was doing. Any advice for us in that circumstance? – Bobort Feb 12 '21 at 16:51
  • Okay, I had to use `sudo` to run the `pg_dropcluster` command. In my case: `sudo pg_dropcluster --stop 10 main` – Bobort Feb 12 '21 at 16:59
8

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
Jenny D
  • 27,780
  • 21
  • 75
  • 114
  • 1
    No, please don't. On Ubuntu at least that'll cause a hideous mess because it expects you to use its own wrapper tools. – Craig Ringer Feb 11 '14 at 00:36
  • 1
    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. – Jenny D Feb 11 '14 at 05:48
  • @Craig: what sort of mess would it create? I've just used this method on Ubuntu, and [it seems fine to me](http://serverfault.com/a/695343/69930). Would you add some detail to your answer? – halfer May 29 '15 at 17:37
  • 1
    @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. – Craig Ringer May 30 '15 at 00:35
  • what if this is a remote database that we've connected to and not one that we've initialized locally? – fIwJlxSzApHEZIl May 09 '18 at 15:59
  • @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. – Jenny D May 09 '18 at 19:44
5

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

i-CONICA
  • 648
  • 1
  • 9
  • 22