Re: Is it possible to keep indexes on different disk location?

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