Hello all, If I were in your situation, I would analyze if it could move only a part of the 36 databases to the new disk. * Either, I will move some of the databases to the new disk, * Either, In the largest databases, I will consider to work in multiple tablespace configuration, using the command ALTER TABLE <<TableName>> SET TABLESPACE <<TablespaceName>>; Link to the documentation: https://www.postgresql.org/docs/9.6/sql-altertable.html . to move some tables to the new disk. You can analyze (depending on your disk and DB configurations, if it's better to move the very large tables or intensively used tables.
I hope I'm clear enough! Feel free to ask for clarification or add new elements to go further on! Hope this helps, Have a nice day, Thomas Le mer. 20 févr. 2019 à 21:37, Ron <ronljohnso...@gmail.com> a écrit : > On 2/19/19 5:02 PM, Julie Nishimura wrote: > > Hello, we are almost out of space on our main data directory, and about to > introduce new directory to our cluster. We cannot use multiple physical > disks as a single volume, so we are thinking about creation new tablespace. > Our current data_directory shows as follows: > /data/postgresql/9.6/main > postgres=# SELECT spcname FROM pg_tablespace; > spcname > ------------ > pg_default > pg_global > (2 rows) > > We also have 36 existing databases on this cluster. > If we add new directory, will it be enough to execute the following > commands in order to force new data there: > > CREATE TABLESPACE tablespace01 LOCATION '/data/postgresql/9.6/main01'; > > ALTER DATABASE db_name SET TABLESPACE > > tablespace01 > > Do I need to repeat it for all our existing databases? > > > Since the command is ALTER DATABASE <your_user_db>, it seems that yes you > have to do it for all of them. A simple bash script should knock that out > quickly. > > > Should I change our "template*" dbs as well? > > > If you want *new* databases to automatically go to tablespace01 then > alter template1. > > Do I need to do something else? > > > Maybe, depending on the size of your databases, and how much down time you > can afford, > > > https://www.postgresql.org/docs/9.6/sql-alterdatabase.html "This command > physically moves any tables or indexes in the database's old default > tablespace to the new tablespace." > > For example, our multi-TB databases are so big that moving it all at once > is unreasonably slow. And a failure might leave the db is a bad spot. > Thus, I'd move one table at a time, a few per outage. > > Naturally, YMMV. > > Thank you for your advises. > > > > > -- > Angular momentum makes the world go 'round. >
