logoalt Hacker News

eugercek • yesterday at 6:12 PM • 4 replies • view on HN

If you use xfs (+`file_copy_method=CLONE`) you can do this with Postgres 18.

`CREATE DATABASE clankerdb TEMPLATE sourcedb STRATEGY=FILE_COPY;`.

But Ardent can be useful for many, because cloud providers uses heavily restricted Postgres. And many use Aurora, which doesn't event let configure the `log_line_prefix`.

Though if cloud providers add file_copy_method=CLONE compatible managed pg ...

ref: https://boringsql.com/posts/instant-database-clones/

Replies

the__alchemist • yesterday at 11:29 PM

Here's how I do it with Heroku. Are there some cloud services that don't have an equivalent?

  heroku pg:backups:capture --app x
  heroku pg:backups:download --app x
  pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d y local_db_for_robots_etc.dump
This takes more than 6 seconds. I'm curious how they achieved that for arbitrary DBs!
➕ show 1 reply
nijave • today at 1:39 AM

A little slow but on Aurora you can attach then promote read replicas. Iirc that's around 20 minutes but I haven't tested recently.

I'd think you could also setup logical rep to a VM then snapshot and clone the storage which is generally pretty fast.

➕ show 1 reply
mnahkies • yesterday at 8:28 PM

I wanted to try doing something similar to this in our dev environment (think shared dev database but per branch clones), but this limitation seemed tricky to accept:

> The source database can't have any active connections during cloning.

I wouldn't mind some lock contention, but having to kill all connections seemed a bit harsh

whalesalad • today at 1:30 AM

Oh nice, the `/var` part of my workstation is a dedicated nvme drive and it's coincidentally formatted as xfs.