0

I have created all tablespaces (only tables created in these tablespaces) in mounted partition, But db is available in pg_default tablespace (root partition where postgresql installed).

I am facing problem with memory since my root partition having only 10 GB. I have verified the data folder (/opt/postgresql/9.1/data/), and noticed few things.

  1. /opt/postgresql/9.1/data/base directory having more data
  2. pg_largeobject table is having more data

So, I planned to do following activity,

  1. Take the full backup of existing database(s) - custom format (*.sql)
  2. Uninstall the postgresql from the root directory, using uninstall.sh (which is available the postgresql path)
  3. Install the new postgresql in mounted path (/newly/mounted/remote/path/postgresql/)

If I do above steps, will postgresql service start and work without any issue?
(if no issue, I will restore the database backup)

Any suggestion?

(using postgresql-9.1)

3
  • So your mounted partition is a remotely stored one? Do you happen to have other local partitions? Why cannot you simply install the database there? Did you think about what happens to the database when someone simply unplugs the external storage where your data (and, planned, the DB engine) are?
    – dezso
    Nov 4 '15 at 23:45
  • @dezso, yes it is remotely stored one. And I agree with your point. So, I need to find a way to cleanup the data which is more or should increase the root partition size in my case?
    – Spike
    Nov 5 '15 at 10:29
  • I'd suggest to move the data directory to another (bigger) local partition.
    – dezso
    Nov 5 '15 at 10:33
0

Yes, you can move your base directory to any location you wish where the postgres service has write access and enough storage available. There are a few things to note

  1. your startup script needs to set it's PGDATA directory correctly to the new location
  2. your clients that connect locally have to set their PGDATA to the new location. Tablespaces in postgres add a lot to complexity. Better is to try to prevent them being used at all.

Tablespaces are directories that are pointed to by symbolic links in $PGDATA so you can keep them if you like. They will be resolved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.