Deleting Old Files in PostgreSQL After Changing Data Directory

postgresqlpostgresql-9.3

Very recently, I changed the data_directory of posgresql (9.3.10) to point to a new data disk I recently added to my VM (Ubuntu 14.04 OS). All the old data has been transferred to this new disk as well.

Prior to this shift, the data_directory was residing in the disk that had the OS installed too. This was a digestible arrangement initially, but ultimately the data_directory almost came to fill the entire 30GB space available. Thus I made the shift.

My question is: can I delete the contents of the old var/lib/postgresql/9.x/main/ folder now that data_directory is entirely pointing to a new location?

The old files are still intact, taking a ton of space, and the only reason I haven't deleted them is because other elements in the postgresql config (or some daemon scripts) might be referring to this folder structure somehow. I'm not sure. Someone with experience can perhaps help out here.

Best Answer

There shouldn't be any references as all the scripts/utils/etc use the data directory environment variable.

the only place I would look is in the new configuration file - take a look you don't have any includes that point to the old folder (not sure at what version they added this option.)

BTW, it's always a good practice to rename (mv dir dir_bck) the folder for a week to see you really don't have anything trying to access there. (you shouldn't anyway as it's stale data...)

Regards,
- Jony

Related Solutions

Postgresql – Why is postgresql still consuming space on the other drive

I do not have all information for pointing to the source of your problem, but I think you should create your postgresql cluster in a different way. Instead of using the postgresql generic command pg_ctl, you should use the Debian/Ubuntu provided command pg_createcluster. This way you will have a working setup that survives reboot, you may specify your data directory and your log directory, you may also specify the start policy in order to activate it automatically on boot. And you may use all cluster commands: pg_lsclusters, pg_upgradecluster, and so on.

Every cluster uses three directories:

/etc/postgresql/8.4/CLUSTERNAME, for its configuration
your data directory, for all your data and WAL (default /var/lib/postgres/8.4/CLUSTERNAME)
your log directory, for your logs (default /var/log/postgresql)

Postgresql – Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

If you suspect there's something wrong with our database (cluster), make a copy of your database directory before you continue. Just to be sure. On Ubuntu the default directory would be: /var/lib/postgresql/9.3/main/ - but your installation may differ.

Actually, your command should work (in default Ubuntu installations), because the service command starts the postgres server process with appropriate privileges (as system user postgres)

sudo service postgresql start

Translates to something like:

sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" start

That starts all db clusters you may have. To start a particular one, use the wrapper pg_ctlcluster:

sudo -u postgres pg_ctlcluster 9.3 main start

To get the status of all currently installed db clusters use pg_lsclusters:

sudo -u postgres pg_lsclusters

You must be aware that the data directory is not the same as the config directory in default Debian or Ubuntu Postgres installations. For Postgres 9.3 that would be:

data dir: /var/lib/postgresql/9.3/main/
config dir: /etc/postgresql/9.3/main/

You can check the /etc/postgresql/9.3/main/postgrsql.conf for the actual data dir. It's the line starting with

data_directory =

The error message indicates that Postgres tries to start with the default data dir, but it is not there or the privileges have been changed. Did you check?

Or are you running a non-default installation?

Best Answer

Related Solutions

Postgresql – Why is postgresql still consuming space on the other drive

Postgresql – Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

Related Question