PostgreSQL – Deleted Postgres Data Directory

postgresql

We recently moved the data directory of our database to a new hard drive because the old one was full.

I needed to clean up some space in order to install something, so I deleted the old data directory.

Now postgres doesn't start anymore, presumably because I deleted the postgresql.config

There are no logs in /var/log/postgres/....

The data directory we copied to the new hard drive doesn't contain a postgresql.conf, i don't know why though.

Deleting the old data directory also didn't free any space even though the folder was 21GB, so I cannot use initdb to create a new config and point it to the new data directory because the disk is full.

Is there any way I can get the system back up and running? We are using postgres 12, if that matters.

Best Answer

Postgres will indeed refuse to start if the postgresql.conf file does not exist, although I'm also not sure why your data directory doesn't include one. Are you sure you didn't accidentally delete it?

Regardless, your Postgres package should have come with sample configuration files, including a postgresql.conf.sample. If you built it from source with the default options, you can find it in the /usr/local/pgsql/share directory. The Arch Linux package manager install puts these "architecture-independent support files" in the /usr/share/postgresql directory (this is what the pg_config utility calls them).

You can see where your particular installation was configured to put them by querying the pg_config utility with the --sharedir option. The server does not need to be running, and I temporarily renamed my own postgresql.conf file to see whether I could still query pg_config, and I could, so I don't know whether this is a viable option for you, unfortunately, sorry about that.

To actually be able to create a configuration file and initialize the postmaster daemon though, have you tried runninglsof +L1 | grep deleted to see if any active processes still hold a reference to the old data directory? This always seems to be the problem when unlinking an inode (i.e., deleting a file/folder) doesn't actually free up the corresponding space.

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Postgresql – Moved PostgreSQL 8.4 tables to a tablespace on an SSD, but still getting 4MB/s writes to old HDD

If you're only talking writes, that's probably because your transaction log in the pg_xlog directory is still on the old drive. I'd suggest moving the tables back to the regular harddrive and move the pg_xlog directory (which is the one that takes sync writes) to the SSD.

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

Postgresql – Moved PostgreSQL 8.4 tables to a tablespace on an SSD, but still getting 4MB/s writes to old HDD

Related Topic