Restore default PostgreSQL maintenance database ‘postgres’

I guess one of the ways you learn is when you screw up – I had such an opportunity today when I tried to restore a PostgreSQL database via a script (using "psql"); I forgot to define a database name and ended up dumping a load of tables and stuff into the "postgres" database.

Anyway, here’s how to restore the default PostgreSQL maintenance database "postgres" under Xubuntu 8.04 – but please check the code below and backup your existing database directory and config files before you try it!

su
/etc/init.d/postgresql-8.3 stop
rm "/var/run/postgresql/8.3-main.pid"   # In case there's an old pid file or postgresql-8.3 init.d stop will complain...

# Backup the existing db
cd /var/lib/postgresql/8.3
mv main main_bak

cd /usr/lib/postgresql/8.3/bin
sudo -u postgres ./initdb --pgdata /var/lib/postgresql/8.3/main

cd /var/lib/postgresql/8.3/main
rm *.conf       # Force system to use the config files in /etc/postgresql/8.3/main
# The following 3 files existed in the default installation
ln -s /etc/postgresql-common/root.crt .
ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
ln -s /etc/ssl/private/ssl-cert-snakeoil.key server.key

/etc/init.d/postgresql-8.3 start

sudo -u postgres psql -c "ALTER USER postgres WITH ENCRYPTED PASSWORD '[Password]';"
Advertisements
Posted in Tips. 2 Comments »

2 Responses to “Restore default PostgreSQL maintenance database ‘postgres’”

  1. Richard Rowlands (@ring22ding) Says:

    This post really helped me out in a major way. Thanks a bunch!

  2. jlinkels Says:

    I did exactly the same to ruin my postgresql 9.1 database on Debian Linux Mint 14. Restore was slightly different.

    su
    /etc/init.d/postgresql stop
    rm “/var/run/postgresql/9.1-main.pid” # In case there’s an old pid file or postgresql-8.3 init.d stop will complain…

    # Backup the existing db
    cd /var/lib/postgresql/9.1
    mv main main_bak

    cd /usr/lib/postgresql/9.1/bin
    sudo -u postgres ./initdb –pgdata /var/lib/postgresql/9.1/main

    cd /var/lib/postgresql/8.3/main
    rm *.conf # Force system to use the config files in /etc/postgresql/8.3/main
    # The following 3 files existed in the default installation
    # In Mint/9.1 this symbolic link should not be there.
    # ln -s /etc/postgresql-common/root.crt .
    ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
    ln -s /etc/ssl/private/ssl-cert-snakeoil.key server.key

    # However it was necessary to restore the file postmaster.opts
    # I restored it from the backup as I could not find it elsewhere

    cp ../main_bak/postmaster.opts
    chown postgres:postgres postmaster.opts

    /etc/init.d/postgresql-8.3 start

    # I did not use the line below and left the postgres user with the password as set by the installer.
    # Note that the administrator does NOT know the password for the user postgresql
    # sudo -u postgres psql -c “ALTER USER postgres WITH ENCRYPTED PASSWORD ‘[Password]‘;”

    Thanks for posting this, although I was in the testing phase, it saved me from purging postgresql and reinstalling.

    jlinkels


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: