iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.
PostgreSQL logical replication connects two ordinary PostgreSQL databases in a publisher–subscriber relationship. The publisher exposes selected tables through a publication; the subscriber connects through a subscription, copies the existing rows, and then applies subsequent changes in transaction order. This guide shows how to configure both VPS servers, secure the connection, create the publication and subscription, verify health, and recover from common failures.
Understand the topology before changing PostgreSQL
Choose one VPS as the publisher and the other as the subscriber. The publisher writes changes to its write-ahead log (WAL) and makes the selected tables available through a publication. The subscriber pulls those changes and applies them to tables that already exist in its database.
Logical replication is selective: you choose databases, tables and, where appropriate, the data-change operations to publish. A subscriber can also publish its own tables, which makes cascading designs possible. It is different from a physical standby because it does not copy the entire cluster byte-for-byte.
- Both hosts need reachable PostgreSQL services and compatible table definitions.
- Different major PostgreSQL versions and operating systems can be used, but you must still review data types, extensions, collations and operational compatibility.
- Replication is database-local. A publication created in one database is not visible in another database on the same server.
- Initial synchronization copies current table contents. After that copy completes, ongoing changes are streamed and applied in publisher transaction order within the subscription.
Plan capacity and prerequisites
Replication consumes connection slots, replication slots, WAL storage and background workers. Set capacity before creating a subscription; changing these parameters later can require a restart and an outage.
#1 Best Overall
- Powerful Turbo Fan:CAGIWIRU Electric Air Duster have reached speed up to 130000RPM,Can efficient remove dust and debris.With LED light and Three Adjustable Speed to meet with different cleaning tasks.
- With Attached USB C Fasting Charging cable.Charing 3 hours,Enjoy up to 240 minutes of use on the lowest setting, For third speed can use for 25mins.with four charging options to meet with your needs.
- Wide Application:Upgraded attached 5 different nozzles,making it suitable for a variety of scenes, such as car,pet, pc, keyboards, and other electronic devices. It also serves for office and home clean duster.
- Handy Design & Portable:Cordless air duster electric nozzle is Light weight and small size, design and comfortable to hold and space saving, convenient to carry around. You can put it in your room or inside the car without taking up space.
- Warranty & Support: Our Electric air duster comes with 5 years warranty and 24/7 customer service. During this period, if you have any questions,pls kindly feel free to contact us.
| Server | Parameter | How to size it |
|---|---|---|
| Publisher | wal_level |
Set to logical. |
| Publisher | max_replication_slots |
Allow one slot for each expected subscription plus reserve for synchronization. |
| Publisher | max_wal_senders |
Allow at least the replication-slot count plus any physical-replica senders. |
| Subscriber | max_replication_slots |
Allow the subscriptions you will create plus synchronization reserve. |
| Subscriber | max_logical_replication_workers |
Reserve workers for subscriptions, table synchronization and any parallel apply work. |
| Subscriber | max_worker_processes |
Provide enough workers for logical replication and the rest of the database workload. |
These are capacity relationships, not performance guarantees. Do not choose a universal lag target or VPS size from a generic checklist. Measure your write rate, initial-copy volume, network throughput, disk speed and available WAL storage.
Prepare the publisher VPS
1. Confirm the database and network endpoints
Record the publisher database name, its private or public address, the PostgreSQL port (normally 5432), and the subscriber’s source address or CIDR. Test TCP reachability from the subscriber to the publisher before troubleshooting PostgreSQL authentication. A provider firewall or security group must permit the PostgreSQL port only from the intended subscriber network.
Free tools Windows power users keep installed
One-click scans. No signup required.
2. Set logical-WAL parameters
Edit the publisher’s postgresql.conf and set values appropriate for your planned number of subscriptions:
wal_level = logical
max_replication_slots = <subscriptions plus synchronization reserve>
max_wal_senders = <at least max_replication_slots plus physical replicas>Use SQL to see the active values:
SHOW wal_level;
SHOW max_replication_slots;
SHOW max_wal_senders;wal_level and other startup parameters generally require a PostgreSQL restart. Restart using the service manager for your distribution, then run the SHOW statements again to confirm that the running server, not just the file, has the desired values. A configuration reload is sufficient only for parameters documented as reloadable.
3. Create a dedicated replication login
Use a separate account rather than an administrator account. It needs LOGIN and REPLICATION, and it needs SELECT on every table included in the publication so that the subscriber can perform the initial copy.
CREATE ROLE repuser WITH LOGIN REPLICATION PASSWORD 'use-a-secret-manager-value';
GRANT CONNECT ON DATABASE foo TO repuser;Connect to the database that will be published and grant table access there:
c foo
GRANT SELECT ON TABLE users, departments TO repuser;Grant access for each additional published table. If you add a table later, grant its SELECT privilege before adding it to the publication. Keep the password out of shell history, source control and process listings; use your deployment platform’s secret manager.
Rank #2
- Screen Cleaner is Your Screen’s New Bestfriend! - EVEO is proud to present a screen cleaner that’s perfectly suited for your TV. Meet your new screen cleaner, computer screen cleaner, laptop cleaner, iPad cleaner, Macbook,tv cleaner or any other electronic device. Our screen cleaner spray and wipe along with the included plush microfiber cloth, easily removes dust, fingerprints & other smudges on the screen’s surface. Clean effectively for a streak-free screen
- Gentle on your Screens - Our screen cleaner spray and wipes is compatible to be used even for the most sensitive LCD, LED, CRT, and OLED screens in proper use. Be at ease knowing that this product is compatible with all the major brands: LG, SONY, Samsung, Sharp, iPad, iPhone, Android screens, Kindle, PC monitors - we’ve got you covered! Can be used as LCD screen cleaner, tv cleaner, smart tv screen cleaner, and more
- Premium Super Soft Plush Microfiber Cloth - Included in this efficient screen cleaning kit. This screen cleaner spray and wipe has our signature plush microfiber cloth specially designed to comply with screens and monitors and leave them streak-free. Has been shown to effectively absorbs stubborn stains, zero streaks, and no fiber shedding. Always use this together with our screen cleaner for the best results
- Easy-to-Use Screen Cleaner Kit + microfiber cleaning cloth - The EVEO Screen Cleaning Kit will easily be your new favorite tool when it comes to taking care of the things you love most, quick and easy solution for a streak-free screen. Simply screw on the sprayer gun on the screen cleaner bottle. Spray the cleaning liquid onto the microfiber cloth, then wipe the screen until desired cleanliness & shine is achieved.
- Quality Screen Care Kit Worth Purchasing - Enjoy a convenient Screen Cleaner anytime, anywhere you need it. Your satisfaction is our top priority. We stand behind the quality of our products and that’s why, you shouldn’t be worrying at all. We manufacture the Best Screen Cleaner. and can be used for laptop cleaning kit, laptop screen cleaner, tv screen cleaner for smart tv. MacBook screen cleaner, monitor cleaner or screen cleaner spray for electronic devices with microfiber cleaning cloth
4. Allow only the subscriber in pg_hba.conf
Add a narrowly scoped host rule on the publisher. The following shows the shape; replace the CIDR with the subscriber’s actual address range:
host all repuser <subscriber-cidr> scram-sha-256Use the most specific address or subnet possible. Do not expose a broad network when a single private address is sufficient. Ensure the role name, database, authentication method and CIDR match the connection string. Reload PostgreSQL after editing pg_hba.conf; a full restart is not normally required for an access-rule change.
Use encrypted transport whenever your deployment supports it. A firewall rule and pg_hba.conf rule are separate controls: the firewall limits who can reach the port, while pg_hba.conf decides which database users may authenticate.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Prepare the subscriber VPS
1. Create matching database objects
Create the destination database and the tables before creating the subscription. Logical replication does not create a schema for you. Define compatible columns, data types, constraints and indexes, and install any required extensions on the subscriber independently.
CREATE DATABASE foo;Connect to foo and create the destination tables with the same names and compatible definitions as the published tables. The initial synchronization inserts rows into these tables; it does not migrate your schema files.
2. Reserve slots and workers
Set the subscriber’s capacity in postgresql.conf:
max_replication_slots = <subscriptions plus synchronization reserve>
max_logical_replication_workers = <subscriptions plus sync/parallel reserve>
max_worker_processes = <logical workers plus other workers>The worker total must include unrelated background workers used by your workload. Restart when a parameter requires it, then verify the effective values with:
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Rank #3
- 【Large size】：Measuring 800 x 300 mm/ 31. 5×12 inches, which is wider and taller than others. Large mouse pad can perfectly fit your keyboard and mouse of any size, and there is also ample space for peripherals such as phones, tablets, etc.
- 【LED backlight mouse pad】：Blade Hawks RGB mouse pad provides 7 static modes and 7 dynamic modes will give you the options you are looking for. Power-off memory function remembers the last lighting mode you selected. The super glow fiber Chroma will give you a colorful gaming setup that you want.
- 【Anti-slip rubber base】：Made from Natural rubber, this gaming mousepad will firmly grip your desktop, allowing you to enjoy the ultimate precision in the games you are most passionate about. It also comes with a texture that enhances this ability even further.
- 【Smooth and waterproof surface】: Micro-textured cloth surface, for extremely precise mouse control and a smooth movement. When liquid splashes on the gaming mouse pad, it forms water droplets and slides down. Will not delay your work or gameplay.
- 【Plug and play】: This mouse and keyboard pad is powered by USB, plug and play, no driver required. One button control light modes. Press one time to change the lighting mode, press twice to change the brightness, press and hold about 3 seconds turn ON/OFF.
SHOW max_replication_slots;
SHOW max_logical_replication_workers;
SHOW max_worker_processes;If this subscriber might become a failover target, plan separately for sequence values, schema deployment and objects that logical replication does not carry.
Check replica identity before publishing changes
PostgreSQL needs a way to identify the target row for an UPDATE or DELETE. A primary key is the normal replica identity. Check every published table that can receive those operations on both servers, and make sure the corresponding key exists on the subscriber.
SELECT n.nspname AS schema_name,
c.relname AS table_name,
c.relreplident AS replica_identity
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind = 'r';If a table has no practical key, you can choose REPLICA IDENTITY FULL:
ALTER TABLE public.some_table REPLICA IDENTITY FULL;This records enough old-row data to find a matching row, but subscriber searches can be inefficient, particularly for large tables. Treat it as a fallback, not a replacement for a meaningful primary key or other suitable identity.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Create the publication on the publisher
Run the command in the publisher database that contains the tables:
CREATE PUBLICATION mypub FOR TABLE users, departments;This publishes the listed tables’ changes. Publications can be database-local and can serve multiple subscribers. You may restrict which data-change operations are published or add tables later with ALTER PUBLICATION. For example, adding another table requires both the publication change and a compatible table plus privileges on the subscriber.
ALTER PUBLICATION mypub ADD TABLE public.audit_events;Adding a table does not replace the need to coordinate its schema and permissions. Decide whether existing rows should be copied for the newly added table when you create or refresh the subscription, and verify the resulting synchronization state.
Rank #4
- 【Softer and More Comfortable】Vaydeer wrist rest has unique diamond pattern, which is the combination of softness and aesthetics. The materials of wrist rest are improved into higher quality memory foam and covered with silky smooth lycra. The computer wrist rest makes you as comfortable and cushiony as like rest your wrists on clouds.
- 【Ergonomic Wrist Saver】The wrist rests for keyboard and mouse comes with a 17.32×3.15×0.83 inch keyboard wrist pad and a 5.94×3.15×0.83 inch mouse wrist support. Based on ergonomic design, the unique concave shape is the perfect fit for your wrist joints. The wrist rest pad fits most computer keyboards and laptops, improve hand and wrist posture, release your wrist and arm stress.
- 【Non-Slip Rubber Bottom】Featuring an anti-skid silicone base on the bottom, this wrist keyboard support stays firmly in place on your desk, preventing the padding from sliding around, ensuring stable and consistent wrist support during extended computer sessions.
- 【Better Experience & Pain Relief】Our keyboard arm rest is beneficial to alleviate the soreness caused by direct contact and friction between your arm and a hard desk surface, reducing the risk of wrist fatigue or carpal tunnel. The soft texture of memory foam can evenly distribute the pressure around your wrists and provide good support with just enough give.
- 【Helpful in Multiple Scenarios】Whether you're working, studying, writing, typing, gaming, this keyboard and mouse rest combo is an essential accessory to add comfort and support to your hands and wrists. It’s also a great gift for men, women, family, friend, coworker, gamer, teacher, etc.
Create the subscription on the subscriber
Run this command in the subscriber database. Replace the host, database and role values, and supply the password through a protected deployment mechanism:
CREATE SUBSCRIPTION mysub
CONNECTION 'dbname=foo host=publisher.example user=repuser password=REDACTED sslmode=require'
PUBLICATION mypub;Creating a subscription normally creates or uses a replication slot on the publisher, starts an initial table synchronization and then applies incremental changes. The initial copy can take considerably longer than steady-state apply; its duration depends on row count, indexes, network bandwidth, disk performance and concurrent writes.
Do not put a real password in shell history, a ticket, a source repository or an unprotected configuration file. If your connection requires certificate verification, use the SSL settings and certificate paths appropriate to your PostgreSQL installation rather than relying only on encryption without identity verification.
Validate the initial copy and ongoing apply
Inspect subscription workers
On the subscriber, inspect the subscription statistics view:
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteSELECT subname,
pid,
relid,
received_lsn,
latest_end_lsn,
latest_end_time
FROM pg_stat_subscription;An enabled subscription normally has an apply worker. Additional workers can appear during initial table synchronization or parallel apply. An empty result, a missing process identifier or an old end time warrants checking the server log and subscription state.
Check conflicts and errors
Use the subscription statistics view for conflict counters:
SELECT *
FROM pg_stat_subscription_stats;Read the PostgreSQL server log at the same time. It usually identifies the table, constraint, permission or connection error that prevented apply. A successful connection alone does not prove that rows are being applied; confirm worker activity and advancing timestamps or LSN values.
Best Value
- 【No Drill Mounted】Cable management tray can be assembled in as little as 3 minutes or less-simply. Side clips easily clampe the edge of desk, pass through the holes on the sides, and secure excess cables with baskets. Desk cable management supports inward or outward installation to meet your needs in different scenarios, and provides you with great convenience when collecting and organizing wires.
- 【Rubber Pad Protects Desk】Wire organizer under desk built-in soft rubber pads on the clamp protect your desk from scratches, so you will not damage your furniture or office.
- 【Sturdy and Beautiful】Cord management under desk is made of high-quality carbon steel and holds about 15 lbs. The wire basket has 2 holes on each side of the tray to enable your cables to pass freely. Cable baskets are suitable for desk thickness use from 0.4" to 2.4".
- 【Space Saving & Keep Organized】Desk cable management tray holds all your power cords, outlet strips, USB hubs and computer transformers hidden from your feet, and the data cables will not enter the vacuum cleaner to keep your desk clean and tidy. Desk wire organizer is also suitable for use in the kitchen and outdoors.
- 【Reduce Safety Risks】Cable tray under desk keeps all plugs away from your kids, no more worry about tripping. Using 2 holes wire tray saves extra space, helps you easily cabling and wire protection. The mesh design is not easy to accumulate dust, but avoids safety accidents caused by messy wires.
Watch replication slots and WAL retention
On the publisher, inspect slots:
SELECT slot_name,
slot_type,
active,
restart_lsn,
confirmed_flush_lsn
FROM pg_replication_slots;An inactive subscription can keep a logical slot from advancing. The publisher must retain WAL needed by that slot, so prolonged inactivity can consume disk space. Alert on an inactive or missing expected slot, growing WAL retention, connection failures and sustained apply delay. There is no universal numeric lag threshold; set alerts from your workload’s recovery objectives and available storage.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteResolve stopped replication safely
Authentication or network failure
- Confirm the subscriber can reach the publisher’s PostgreSQL port.
- Check the provider firewall or security group and the publisher’s listening address.
- Verify the
pg_hba.confCIDR, role name, database name and authentication method. - Confirm the password or certificate supplied to the subscription is current.
- Reload
pg_hba.confchanges and inspect the server log for the exact rejection.
Permission failure during initial copy
The replication role needs SELECT on every published table, and it must be able to connect to the database. Grant the missing privilege on the publisher, then resume the subscription after confirming the error has cleared. Also check that the subscriber’s destination tables permit the apply worker to insert, update and delete rows as required by your table ownership and permission model.
Constraint or data conflict
Subscriber-side writes can collide with incoming changes. Duplicate keys, foreign-key violations, row-level conflicts and permission errors can stop the apply worker. Identify the offending transaction in the log, correct the subscriber data or permissions, and then resume the subscription. Skipping a transaction is an advanced action: it can leave the subscriber inconsistent unless you reconcile the affected data afterward.
Capacity or disk pressure
If a slot retains increasing WAL, determine why the subscription is inactive before deleting anything. A slow initial copy, an exhausted worker pool, a full disk or a network outage can all prevent progress. Add capacity or fix the underlying condition, then verify that the slot’s confirmed position and subscriber’s latest apply time begin advancing. Do not drop a slot casually; doing so discards the publisher’s retained position and requires a deliberate resynchronization plan.
What logical replication does not replicate
Logical replication carries row changes, not an entire PostgreSQL environment. The database schema and DDL commands are not replicated. Plan a migration or deployment process for table creation, column changes, indexes, constraints, functions, permissions and extensions.
Recommended Free Tools
- Sequences: sequence state is not kept synchronized by row-change replication. A promoted or writable subscriber needs an explicit sequence-management plan.
- Large objects: large-object data requires separate handling.
- Schema changes: apply compatible DDL on both sides in a controlled order before publishing writes that depend on it.
- Conflicting local writes: a subscriber that accepts application writes needs conflict detection, ownership rules and reconciliation procedures.
Backups remain necessary. Replication can faithfully copy an unwanted delete or corruption, so it is not a substitute for point-in-time recovery.
Choose a VPS design that matches the workload
| Design | When it fits | Important operational work |
|---|---|---|
| One-way read copy | Separate read traffic from a write-oriented publisher. | Keep the subscriber’s local writes controlled; monitor apply delay and WAL retention. |
| Selective table replication | Share only specific tables or data between systems. | Maintain matching schemas, privileges and replica identities for every selected table. |
| Cross-version migration | Move selected data between supported PostgreSQL major versions or operating systems. | Review data types, extensions, DDL, sequence state and the cutover procedure. |
| Cascading replication | Have one subscriber publish onward to another system. | Capacity-plan workers and slots at every hop and monitor each link independently. |
For any design, estimate the initial-copy size and acceptable catch-up time, reserve WAL and disk headroom, document conflict and failover handling, and assign ownership for schema and sequence synchronization.
Operational checklist
- Publisher has
wal_level = logicaland enough replication slots and WAL senders. - Subscriber has enough replication slots, logical replication workers and total worker processes.
- Private networking or a narrowly scoped firewall rule limits PostgreSQL access to intended peers.
pg_hba.confallows only the dedicated replication role from the subscriber’s address range.- The role has
LOGIN,REPLICATION, database connectivity andSELECTon all published tables. - Destination databases, tables, keys, extensions and permissions exist before subscription creation.
- Every table receiving updates or deletes has a primary key or deliberate replica identity.
- Subscription credentials are stored in a secret manager and encrypted transport is configured.
pg_stat_subscription,pg_stat_subscription_stats, server logs andpg_replication_slotsare part of routine monitoring.- Schema, DDL, sequences, large objects, backups and conflict recovery have documented procedures.
Final decision
Logical replication is a practical way to move selected PostgreSQL tables between VPS servers while keeping the systems independently addressable and potentially on different major versions. A reliable deployment depends less on the two SQL commands than on the surrounding work: correctly sized slots and workers, restricted network access, replica identity, matching schemas, protected credentials, WAL monitoring and a plan for conflicts and non-replicated objects. Build and test those controls before treating the subscriber as a production read target or failover system.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
