restore specific database only
"The selected branch main contains multiple databases. If you restore the branch, all databases will be restored to the same point in the past."
Is there a specific suggested strategy to restore only specific databases within a branch. I am using one DB per customer and was wondering what are the options to allow restoring a single database only in the event something goes wrong for just one of them.
7 Replies
harsh-harlequin•2mo ago
Neon's restore capabilities are at the branch level. If you've defined multiple Postgres databases on a single branch, you could manually create backups of the individual databases using pg_dump.
https://neon.com/docs/manage/backup-pg-dump
Alternatively, to take advantage of Neon's restore capabilities, you could move to a branch or project per customer setup. We typically recommend a project per customer, as this isolates data and resources. Of course, this comes with some management and perhaps compute cost tradeoffs — and may not be ideal for all use cases. Is there a reason you chose to create separate databases for each customer on the same branch vs using separate branches or projects? We'd be interested in hearing about your use case.
Neon
Backups with pg_dump - Neon Docs
This topic describes how to create a backup of your Neon database using the Postgres pg_dump utility and how to restore a backup using pg_restore. Avoid using pg_dump over a pooled connection string (...
like-goldOP•2mo ago
Hi Daniel, it could be I misunderstood the concept of branches. I thought of the branches are like versions of the database, I think you had the concept of production and development explained somewhere. But if it would be sensible to have a branch for each customer... maybe that could be the case. I am just trying to keep things flexible in general but the current requirement would be to ensure data is isolated, hence I use a database per customer. and ideally being able to restore each customers database (just thinking in case something is mess up just for one customer account etc.)
harsh-harlequin•2mo ago
Branches are separate instances of Postgres.
Projects can have one or more branches.
Branches can have one or more Postgres databases.
Have a look here:
https://neon.com/docs/manage/overview
For database per-customer setups, we generally recommend a project per customer to keep both data and resources (storage and compute) separate.
You'll need at least separate branches to let each customer restore data in isolation.
This is worth a read:
https://neon.com/use-cases/database-per-tenant
Also, there's a series of posts on this topic from Dian M Fay on our blog site: https://neon.com/blog/multi-tenancy-and-database-per-user-design-in-postgres
like-goldOP•2mo ago
OK awesome, will dig more into branches and look at the db/customer setup as well. Thanks a lot for this I will take a look and study.
Hi @Daniel I have an additional question here for connection string management. Not sure if you guys have a solution here, I could not find something specific.
The two options I see right now is to either compute them once using await neonApiClient.getConnectionUri and store them on my end.
Or, I compute them on each request using await neonApiClient.getConnectionUri
harsh-harlequin•2mo ago
@arauser The URIs remain valid for the life of a branch unless you remove & re-add the compute. It might make sense to store the URI. On the other hand, our API fully supports retrieving the URI with each request (within API rate limits). @Andre Landgraf might be able to provide better advice here, though.
https://api-docs.neon.tech/reference/api-rate-limiting
Neon
API rate limiting
Neon limits API requests to 700 requests per minute (about 11 per second) , with bursts allowed up to 40 requests per second per route, per account. If you exceed this, you’ll receive an HTTP 429 Too Many Requests error. These limits apply to all API requests, including those made by the Neon Consol...
absent-sapphire•2mo ago
Hey @arauser, great question. I would probably store the project/branch identifiers for each customer in a meta database and the connection string in a cache with failover to refetch in case the credentials changed for whatever reason - but I think this also very much depends on your setup. Caching sounds good to me in theory but it really depends on your request/response flow and setup! 🙂 May not be needed
like-goldOP•2mo ago
Thanks @Andre Landgraf that sounds like a plan!