Single-Server PostgreSQL: When a Business Needs a Standby

calendar Oct 25, 2026 / · 9 min read · postgresql high availability cloud sql  ·
Share on: linkedin copy

A small business keeps its orders, invoices and customer list in one PostgreSQL server. If that machine stops, nothing takes over. Moving to a managed host does not fix that by itself, because Cloud SQL doesn't recover standalone instances from a zonal outage automatically. PostgreSQL's own streaming replication is asynchronous by default, so a standby does not guarantee zero data loss either.

What a single database server puts at risk

High availability means database servers working together so a second server can take over quickly if the primary fails. A standby that accepts no connections until it is promoted is a warm standby. One that accepts connections and serves read-only queries is a hot standby. In PostgreSQL 18 the hot_standby setting is on by default, so a standby can answer queries while it replays changes from the primary.

The trade-off is timing. Because streaming replication is asynchronous by default, the primary confirms a commit before the standby has received it. The delay is typically under one second, assuming the standby is powerful enough to keep up with the load, and the data lost in a failover is proportional to the replication delay at that moment. An asynchronous setup accepts that some transactions might be lost in the switch to a backup server.

Synchronous replication closes that gap, and PostgreSQL leaves it off. With nothing set in synchronous_standby_names, commits do not wait for replication, and that is the default configuration. Turning it on has a cost: a fully synchronous solution over a slow network might cut performance by more than half, while an asynchronous one might have a minimal performance impact. Listing more than one synchronous standby adds protection against data loss, and each one is another server to buy and run.

Failover and read scaling are different goals. Failover keeps the business running when the primary dies. Read scaling moves reports and dashboards off the primary. On Cloud SQL, the primary reason for replication is to scale the use of data without degrading performance. In PostgreSQL, a reporting server that only offloads read-only queries does not need to be promoted at all.

Running your own PostgreSQL standby

PostgreSQL is released under the PostgreSQL License, a liberal open source licence similar to BSD or MIT, so a second server carries no licence fee. The cost is the machine and the time to run it. Setup starts with a base backup of the primary to bootstrap the standby. The server then enters standby mode if a standby.signal file exists in its data directory when it starts.

On the primary, create a physical replication slot for the standby:

1postgres=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');

On the standby, point it at the primary and at that slot:

1primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
2primary_slot_name = 'node_a_slot'

Three settings on the primary decide whether this keeps working. max_wal_senders defaults to 10, and 0 disables replication. wal_keep_size defaults to zero, which keeps no extra WAL segments for standbys, and that gap is the reason to use a slot. The slot brings its own risk: with max_slot_wal_keep_size at its default of -1, slots can retain an unlimited amount of WAL. A slot that is no longer in use makes PostgreSQL hold onto WAL segments indefinitely, and the disk grows with it. Watch the lag as well: the amount of WAL generated on the primary but not yet applied on the standby is the health number for streaming replication.

Failover is your job

PostgreSQL does not provide the software to identify a failure on the primary and notify the standby. Something outside the database has to watch the primary and make sure the old one is shut off before the standby takes over. Without that step, both systems can think they are the primary, which leads to confusion and ultimately data loss. The promotion itself is one command: pg_ctl promote, or a call to pg_promote().

After the promotion, only one server is running, a degenerate state that lasts until a new standby is built. Write the failover and rebuild steps down before the night you need them.

Two settings protect against other kinds of trouble. With recovery_min_apply_delay set to 5min, the standby replays each commit only once its clock is at least five minutes past the commit time on the primary. That gives a window to stop before a mistaken DELETE reaches the copy. For patching, when updating to a new minor release, update the standby servers first, because a new minor release is more likely to read WAL from an older one than the other way round.

Managed PostgreSQL on Cloud SQL, and what it costs

High availability

A Cloud SQL HA configuration exists to reduce downtime when a zone or instance becomes unavailable. All writes to the primary are replicated to disks in both zones before a transaction is reported as committed, so a committed write already exists in two places. Each second, a heartbeat checks whether the primary is healthy. If several heartbeats are missed, failover starts, and the instance is unavailable for about sixty seconds. In some cases Cloud SQL restarts the instance instead of failing over.

An HA-configured instance costs twice as much as a standalone instance, and that covers CPU, RAM and storage. Cloud SQL comes in two editions, Enterprise and Enterprise Plus, and CPU and memory prices depend on the region. In Iowa (us-central1), Enterprise edition on the General Purpose machine series costs $0.0413 per vCPU hour and $0.007 per GiB of memory per hour, or $0.0826 and $0.014 with HA. Enterprise Plus on the C4 machine series costs $0.054 per vCPU hour and $0.009 per GiB hour in the same region. That puts a 2-vCPU, 8 GiB Enterprise instance in Iowa at about $0.14 an hour standalone and about $0.28 with HA, before storage.

New customers get $300 in free credits for Cloud SQL. Turn on automated backups with HA, and note that moving an instance to a different zone causes downtime.

A standalone instance has no automatic zonal recovery. With point-in-time recovery, its RPO is typically five minutes or less. If you recover by promoting a read replica, the RPO varies with the workload.

Read replicas

A read replica is charged at the same rate as a standard Cloud SQL instance, and the replication itself is free. A cross-region replica adds cross-region data transfer charges for the replication logs. Replicas are read-only, and read replicas don't provide failover capability. The primary can fail over only to a DR replica.

A replica comes with limits. You cannot configure backups on it. Cloud SQL does not load-balance between replicas, so the application has to send its read queries to the replica address itself. The recommended limit is 10 or fewer direct read replicas per primary, and up to four levels are allowed, counting the primary. An undersized replica is prone to out-of-memory events, and read replicas can get a disruptive upgrade at any time.

Logical replication for moves and upgrades

Physical replication requires both databases to run the same version. Logical replication works across major versions, which makes it the tool for moving off an old server or onto Cloud SQL. It uses a publish and subscribe model. If anything else writes to the same tables on the subscriber, conflicts can arise.

On the publisher, set this in postgresql.conf. The other required settings have default values that are sufficient for a basic setup:

1wal_level = logical

Allow replication in pg_hba.conf. The PostgreSQL example opens this to every address (0.0.0.0/0); limit it to the subscriber's own address instead (192.168.1.60/32 here is a placeholder):

1host all repuser 192.168.1.60/32 scram-sha-256

Create the publication on the source, then the subscription on the target:

1CREATE PUBLICATION mypub FOR TABLE users, departments;

1CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub;

The subscription copies the initial contents of users and departments, then replicates incremental changes to those tables.

Cloud SQL adds its own rules. You enable logical decoding with the cloudsql.logical_decoding flag set to on, and changing these flags requires a restart. HA instances can't be subscribers because they don't have a consistent outgoing IP address. Creating the subscription needs the cloudsqlsuperuser role. max_wal_senders defaults to 10, and its limit grows by 8 WAL senders per GB of instance memory. From Cloud SQL for PostgreSQL 16, read replicas can act as publishers if the primary has the logical flags set.

Google's nonprofit Cloud credits page does not list Cloud SQL among its sample benefits.

How to choose

Stay on one server if the business can live with losing a few minutes of work and waiting while someone restores it. Keep automated backups and point-in-time recovery switched on. On Cloud SQL, that means an RPO of typically five minutes or less and a manual recovery after a zonal outage. Run one test restore so the wait has a known length.

Run your own hot standby if someone on staff, or an IT partner on call, can write the failover procedure, test it and carry it out at night, with a monitoring tool watching the primary, since PostgreSQL does not detect the failure itself. The software is free, and most of the cost is on-call time.

Pay twice the standalone price for Cloud SQL HA if about sixty seconds of automatic failover is worth more to the business than the extra monthly bill. Add a read replica only when read load is the problem. If you add one for reliability, put it in a third zone.

Key Takeaways

  • If accidental deletes worry you more than a server failing, rely on point-in-time recovery or a standby with recovery_min_apply_delay, because Cloud SQL HA writes every commit to both zones, mistaken ones included.
  • If you are moving into Cloud SQL with logical replication, the target instance cannot have HA turned on while it is a subscriber. Turn HA on after the move is finished.
  • If you create replication slots, give max_slot_wal_keep_size a limit before the first slot goes idle.

References

Posts in this series