The short answer.
Put the application and PostgreSQL on a shared Compose network, publish only the web entry point and mount the data on a persistent volume. Try a restore outside the VPS and measure a load period before choosing memory and storage.
- The application can reach the database on the private Compose network without publishing its SQL port to the Internet.
- A volume keeps the data when the container is replaced. A copy outside the VPS covers the loss of the machine.
- Measure consumption during a backup and during a traffic peak before choosing your configuration.
01Separate the three services
Declare an HTTPS proxy, an application and a PostgreSQL service named db. Connect the proxy to the application on a first network, then the application to db on a second. The proxy then has no direct access to the database. From the application container, the SQL connection targets db on port 5432. The localhost address would refer to the application container itself.
02Keep the database when the container changes
Pin the major version of the PostgreSQL image. Mount a named volume at the data location documented for that version. In pre-production, create a test record, recreate the container while keeping its volume, then read that record back. Store credentials outside the repository and grant the application account only the necessary rights.
03Test the first start and reconnection
Add a health check to db with pg_isready. The service_healthy condition lets Compose wait for this result before starting the application. Also test an interruption after startup. The application must reopen its connection when PostgreSQL comes back. Then perform a read and a write over HTTPS to verify the full path.
04Choose the size with your own measurements
Wisp Spark provides 2 vCPU, 4 GB of RAM and 60 GB of NVMe. Wisp Core offers 3 vCPU, 8 GB and 120 GB. If your test reaches 5 GB of RAM for the whole machine, Spark is already too small for that load. For disk, add up data, indexes, Docker container images, logs and temporary restore space.
05Restore before the first customer order
Export the database with pg_dump in custom format, then keep an encrypted copy outside the VPS. Keep the necessary roles and the files uploaded by users separately. Restore with pg_restore into an empty test database. Check a recent record and its attachment from the application. Time this operation to know your recovery time.
06Decide whether a single machine is enough
A single VPS simplifies exchanges between containers. Its unavailability stops both the database and the site. Compare the measured restore time with the interruption your business accepts. If this duration is excessive, prepare a second machine and a recovery strategy before opening the service. Then test the failover in pre-production.
Sources & references
To explore the technical points further and find the information at its source.