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

Is it possible to keep indexes on different disk location?

From
"W.P."
Date:
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




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

From
hubert depesz lubaczewski
Date:
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




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

From
Laurenz Albe
Date:
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



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

From
"W.P."
Date:
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





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

From
Ron
Date:
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.