0

I have a directory where postgres was writing to a file: 15426233.4

-rw-------  1 postgres sql  1.0G Feb  4 13:41 15426233
-rw-------  1 postgres sql  149M Feb  4 13:41 15426233.4
-rw-------  1 postgres sql  1.0G Feb  4 13:41 15426233.3
drwx------  3 postgres sql   75K Feb  4 13:40 .
-rw-------  1 postgres sql  1.0G Feb  4 13:34 15426233.2
-rw-------  1 postgres sql  1.0G Feb  4 13:28 15426233.1

Initially this file 15426233.4 was under /data5/PG_9.1/15411/15426233.4. Due to disk space getting filled up under /data6 partition I ended up moving to /data8 however normally we run a symlink so that /data5/PG_9.1/15411/15426233.4 would now symlink to /data8/PG_9.1/15411/15426233.4. Due to lack of disk space the symlink creation failed but move still happened. A while later postgres started writing to a new file /data5/PG_9.1/15411/15426233.4. Is there a way I can stop the db, consolidate the data in /data5/PG_9.1/15411/15426233.4 to /data8/PG_9.1/15411/15426233.4, then create a symlink /data5/PG_9.1/15411/15426233.4 that points to /data8/PG_9.1/15411/15426233.4 and restart the postgres db?

Improve this question
1
  • I don't see any references to tablespaces in postgresql.conf. When you say move the entire data directory, are you talking about changing the reference of the tablespace in pg_tablespace? If I move the complete data dir how would postgres know where it should look at data now? Also how can I consolidate the data that is now being written to a new file with the same name as a file in the /data8 partition? Where does postgres store the name of the data file , say 15426233.4? Commented Feb 12, 2015 at 7:22

1 Answer 1

Reset to default
1

Never, ever, mess with individual files in the data directory. And never ever change catalog tables (pg_tablespace) manually. Everything you need to do can be done through SQL or by handling the complete data directory.

You have several ways to move data to a bigger disk:

Tablespaces

When you create a new tablespace you can put that on the other disk. Then move the tables in question to the new tablespace:

create tablespace bigdata location '/path/to/bigdisk';

Then move the tables to the tablespace:

alter table bigtable set tablespace bigdata;

There is no need to mess around with the data directory manually.

Move the data directory

Caution!
Only do this after stopping Postgres!

  1. Once you have stopped the Postgres server, copy the complete(!) existing data directory to the new disk (make sure you have Postgres stopped before you do that). It's safe to copy the data and later delete the original if everything is OK rather than moving it right away.

  2. After copying the data, adjust postgresql.conf to point it to the new data directory: http://www.postgresql.org/docs/current/static/runtime-config-file-locations.html

  3. Rename the old data to make sure you will see an error if the configuration wasn't changed properly.

  4. Start Postgres again.

The physical layout of the disk storage is documented in the manual:
http://www.postgresql.org/docs/current/static/storage.html

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.