Обсуждение: Is it possible to keep indexes on different disk location?
Hi there, I have a PostgreSQL 11 server on OrangePi3 (ARM 64 bit, Armbian, PG from distro). Database (all cluster) is located on USB disk. This approach give me already 2 times loosing DB contents (it is a replica of DB on i7). But the whole thing (mainly indexes) is about 50G, and internal storage is only 32GB. Is it possible to move DB tables etc to this internal storage (sure connection) and put only indexes on USB HDD? And will it help in case of losing connection to USB disk? (DB recoverable instead of total crash)? Laurent
On Thu, Aug 18, 2022 at 08:39:27AM +0200, W.P. wrote: > Is it possible to move DB tables etc to this internal storage (sure > connection) and put only indexes on USB HDD? Sure. There is a thing called tablespace, which is basically, directory where files for db objects reside. You can specify it both when making new objects (https://www.postgresql.org/docs/current/sql-createtable.html, https://www.postgresql.org/docs/current/sql-createtable.html) and you can also move object between tablespaces ( (https://www.postgresql.org/docs/current/sql-altertable.html, https://www.postgresql.org/docs/current/sql-altertable.html). Just keep in mind that moving object means that it will get locked, data copied to new device, and then removed from old, and unlocked. > And will it help in case of losing connection to USB disk? (DB recoverable > instead of total crash)? Well, you need *all* data from all tablespaces to have fully working db. Best regards, depesz
On Thu, 2022-08-18 at 08:39 +0200, W.P. wrote: > I have a PostgreSQL 11 server on OrangePi3 (ARM 64 bit, Armbian, PG from > distro). > > Database (all cluster) is located on USB disk. This approach give me > already 2 times loosing DB contents (it is a replica of DB on i7). > > But the whole thing (mainly indexes) is about 50G, and internal storage > is only 32GB. > > Is it possible to move DB tables etc to this internal storage (sure > connection) and put only indexes on USB HDD? > > And will it help in case of losing connection to USB disk? (DB > recoverable instead of total crash)? I'd say that that is a bad idea. It would not be easy to recover from losing a tablespace, even if it contains only indexes. Get a real computer. Take backups regularly. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
W dniu 18.08.2022 o 16:46, Laurenz Albe pisze: > On Thu, 2022-08-18 at 08:39 +0200, W.P. wrote: >> I have a PostgreSQL 11 server on OrangePi3 (ARM 64 bit, Armbian, PG from >> distro). >> >> Database (all cluster) is located on USB disk. This approach give me >> already 2 times loosing DB contents (it is a replica of DB on i7). >> >> But the whole thing (mainly indexes) is about 50G, and internal storage >> is only 32GB. >> >> Is it possible to move DB tables etc to this internal storage (sure >> connection) and put only indexes on USB HDD? >> >> And will it help in case of losing connection to USB disk? (DB >> recoverable instead of total crash)? > I'd say that that is a bad idea. It would not be easy to recover from > losing a tablespace, even if it contains only indexes. So do You see any (chaeper) solution for using 40GB DB having only 32 (max) GB of storage I can trust? (uSD card) > Get a real computer. Take backups regularly. Main DB is on "real" i7 8GB / 500 SSD. But You are right, I am taking backups irregular. It is not critical, but loosing "main" would not be nice... W.P. > Yours, > Laurenz Albe
On 8/18/22 14:54, W.P. wrote: [snip] > But You are right, I am taking backups irregular. cron job. -- Angular momentum makes the world go 'round.