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.

2

3 Answers 3

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
6
  • 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. Feb 11, 2014 at 0: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, 2014 at 5:48
  • @Craig: what sort of mess would it create? I've just used this method on Ubuntu, and it seems fine to me. Would you add some detail to your answer?
    – halfer
    May 29, 2015 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. May 30, 2015 at 0:35
  • what if this is a remote database that we've connected to and not one that we've initialized locally? May 9, 2018 at 15:59
29

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.
3
  • 2
    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. Apr 23, 2018 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, 2021 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, 2021 at 16:59
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

1
  • 2
    This did not remove existing DB + users (as of Postgres 9.5) Jul 3, 2017 at 14:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .