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.
To create a PostgreSQL database on a Linux VPS, install the PostgreSQL server, create a password-authenticated application role, create a database owned by that role, test a local TCP connection, and only then configure remote access if the application or administrator must connect from another machine. Remote access works only when PostgreSQL is listening on the required interface, the VPS firewall permits the source address, and a matching pg_hba.conf rule allows the user and database.
The commands below use Ubuntu’s packaged PostgreSQL service. Replace <version> with the version installed on your VPS and replace the example names with your own.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →What you need before starting
You need an Ubuntu Linux VPS with sudo access, its server address or DNS name, and a plan for which machines should connect. Decide the following names before running SQL:
appuser: a dedicated login role for the application.appdb: the application database.server-address: the VPS address or resolvable host name used by remote clients.
Do not use the built-in postgres superuser in an application connection string. A dedicated owner limits the damage caused by an application bug or a leaked password. Keep a separate administrative path through sudo and the PostgreSQL administrative account.
#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: 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.
- Economical &Reusable: Built durable materials with a long-lasting battery,our CAGIWIRU air duster it's a sustainable alternative to disposable air cans.
PostgreSQL must be running before a database can be created. Creating the operating-system package alone does not create appdb or an application login.
Install PostgreSQL and verify the service
Install the Ubuntu package
- Connect to the VPS over SSH with an account that can use sudo.
- Install PostgreSQL:
sudo apt install postgresqlThe package creates and starts a PostgreSQL service with usable defaults. Ubuntu’s current defaults use peer authentication for local Unix-socket connections and scram-sha-256 for host (TCP) connections. Older Ubuntu releases used md5 for host connections, so inspect the installed configuration instead of assuming a method from an old tutorial.
Find the active cluster and configuration directory
Ubuntu normally keeps the cluster configuration under:
/etc/postgresql/<version>/main/The files you will use later are commonly postgresql.conf for server settings and pg_hba.conf for client authentication. The version directory is not a literal path; list /etc/postgresql/ if you need to discover the installed version.
Confirm that PostgreSQL is running
Check the service before attempting any SQL:
sudo systemctl status postgresqlA running service should show an active state. If it is stopped, start it and check the status again:
sudo systemctl start postgresql
sudo systemctl status postgresqlIf startup fails, inspect the service journal before editing authentication files. A syntax error in a configuration file, a port conflict, or a damaged cluster must be corrected before a database can be created.
Create a dedicated PostgreSQL role
Open the administrative console
On Ubuntu, the package creates an operating-system account named postgres. Use it to open psql without putting a superuser password in shell history:
sudo -u postgres psqlYou should see a postgres=# prompt. The role created for your application should have only the privileges it needs; it does not need superuser, replication, or role-creation privileges for normal application work.
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
Create a login role with a password
At the psql prompt, run:
CREATE ROLE appuser LOGIN PASSWORD 'replace-with-a-long-random-password';Use a unique secret generated by a password manager or deployment secret store. Avoid shell commands that place the password directly in a process argument or a shared script. In a production deployment, rotate the credential through your application’s secret-management process rather than committing it to source control.
You can use PostgreSQL’s interactive role utility instead. This pattern asks for the password without putting it in the command itself:
Free tools Windows power users keep installed
One-click scans. No signup required.
sudo -u postgres createuser --pwprompt appuserThe utility creates a role; it does not create the application database. You still need the next step.
Check the role
Inside psql, list roles to confirm the login exists:
duQuit the console when you are ready to create the database from the shell:
qCreate the database and assign ownership
SQL method
Reopen the administrative console if necessary:
sudo -u postgres psqlCreate the database explicitly owned by the application role:
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteCREATE DATABASE appdb OWNER appuser;PostgreSQL requires a running server for CREATE DATABASE. Creating a database for another role is privilege-controlled, which is why the command is being run through the administrative account. The owner can later create objects in the database according to the privileges granted by PostgreSQL.
Verify the result with:
lThe database list should show appdb with appuser as its owner. Exit with q.
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.
Shell method with createdb
For provisioning scripts, the equivalent command is:
sudo -u postgres createdb -O appuser appdbcreatedb is convenient when a script already runs shell commands. SQL is clearer when you are expressing a larger transaction of role, database, schema, and privilege changes. Both require the server to be running and an account with permission to create a database for the specified owner.
Recommended Free Tools
| Approach | Best fit | Ownership expression | Operational note |
|---|---|---|---|
CREATE DATABASE appdb OWNER appuser; |
Interactive administration or SQL migrations | Visible in the SQL statement | Runs inside psql as an authorized role |
createdb -O appuser appdb |
Shell provisioning and automation | Provided with the -O option |
Uses the PostgreSQL command-line utility |
Test a local connection before opening the network
Test the application credentials on the VPS before changing firewall or listener settings. This isolates role, password, database, and authentication problems from network problems:
psql -d appdb -U appuser -h localhost -WThe -h localhost option deliberately tests TCP rather than the default local Unix socket. Enter the role password when prompted. A successful connection shows an appdb=> prompt. Confirm the identity and database from inside psql:
SELECT current_user, current_database();Exit with q.
Why peer authentication fails
A command such as psql -d appdb -U appuser normally uses a Unix socket. Ubuntu’s local rule commonly uses peer, which compares the operating-system user with the requested PostgreSQL role. If you are logged in as an OS account that is not mapped to appuser, PostgreSQL rejects the connection even when the database password is correct; the socket path does not ask for that password under a peer rule.
Use -h localhost to make a TCP connection and match a host rule that uses password authentication. Do not “fix” the problem by changing every local rule to trust. The upstream PostgreSQL documentation warns that trust lets anyone who can reach the server log in as any PostgreSQL user, so it is unsafe on an Internet-facing VPS.
Socket versus TCP
| Connection form | Typical command | Authentication rule | When to use it |
|---|---|---|---|
| Unix socket | psql -d appdb -U appuser |
local, commonly peer |
Local administration when OS-to-role mapping is intentional |
| TCP on the VPS | psql -d appdb -U appuser -h localhost -W |
host, commonly scram-sha-256 |
Testing password authentication and application-style connections |
| TCP from another machine | psql --host server-address --username appuser --dbname appdb --password |
A matching remote host or hostssl rule |
Application or administration across the network |
Enable remote connections only when required
Remote access adds three separate controls. All three must agree:
- PostgreSQL must listen on an interface reachable by the client.
- The VPS or cloud firewall must allow TCP port 5432 from the intended source.
pg_hba.confmust contain a matching rule that permits that client, user, and database.
A failure in any one layer produces a different symptom, so change and test them in that order.
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.
1. Set a narrow listening address
Edit the active postgresql.conf, usually under /etc/postgresql/<version>/main/. Set listen_addresses to the specific VPS interface or addresses that must accept connections. Do not expose every interface by default when one private address is sufficient. The exact value depends on your network design; keep the setting as narrow as the application architecture allows.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
After saving the file, PostgreSQL must reload or restart before the setting takes effect. A restart is simple but interrupts existing connections; use a reload when changing a setting that supports reload and reserve a restart for settings that require it.
2. Add the narrowest pg_hba.conf rule
Edit pg_hba.conf in the same configuration directory. A rule identifies connection type, database, user, client address, and authentication method. Prefer a single application source address or tightly bounded network rather than a broad Internet range. For password authentication, use scram-sha-256 on current installations.
A production deployment should prefer an encrypted hostssl rule rather than an unencrypted host rule. Ubuntu’s example uses hostssl and notes that a trusted certificate authority is appropriate for production. Configure a certificate and key according to your organization’s certificate process before requiring hostssl; otherwise clients that do not negotiate TLS will be rejected.
Conceptually, a restricted entry has this shape:
hostssl appdb appuser APPLICATION_SOURCE_IP/32 scram-sha-256Replace the placeholder address with the real application server address or the smallest CIDR range that contains it. Keep the rule above broader rules that might otherwise match first.
3. Understand first-match processing
PostgreSQL examines pg_hba.conf from top to bottom. It chooses the first record matching the connection type, client address, database, and user. There is no fall-through: if that selected record’s authentication fails, later records are not tried. Put specific application rules before broad rules, and remove obsolete entries instead of adding increasingly permissive lines below them.
4. Permit TCP 5432 at the VPS firewall
Allow TCP port 5432 only from the application server’s address or the administrator network that needs access. Do not open the port to all Internet addresses merely to make a test work. If your VPS provider has a separate network security group or cloud firewall, apply the same restriction there; the host firewall and provider firewall both have to permit the traffic.
5. Reload and test from the client
Reload PostgreSQL after configuration changes:
sudo systemctl reload postgresqlIf the listener setting or another change requires a restart, use:
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.
sudo systemctl restart postgresqlFrom the approved remote machine, test with:
psql --host server-address --username appuser --dbname appdb --passwordEnter the password interactively. Once connected, run SELECT current_user, current_database(); and then exit. If the client reports a timeout, check the listener and both firewall layers. If it reports “no pg_hba.conf entry,” the connection reached PostgreSQL but no rule matched the client address, database, user, or SSL mode. If it reports a password failure, verify the role name, secret, and selected authentication method.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchDiagnose common setup failures
| Symptom | Likely layer | What to check |
|---|---|---|
Peer authentication failed for user |
Local socket authentication | Use -h localhost for a password/TCP test, or deliberately configure an OS-user mapping instead of expecting a password to work with peer. |
| Connection refused | Service or listener | Check systemctl status postgresql, the configured port, and whether listen_addresses includes the target interface. |
| Connection timed out | Network firewall or routing | Check the VPS firewall, provider firewall, source address, and network route. A timeout usually means the traffic is not reaching PostgreSQL. |
no pg_hba.conf entry |
Client authentication rules | Check the first matching line’s database, user, source CIDR, and whether the client uses SSL or non-SSL. |
| Password authentication failed | Role credentials | Confirm the role has LOGIN, reset its password through an administrative session if necessary, and ensure the client is using the intended role. |
| Database does not exist | Provisioning | List databases with l and verify that the spelling and selected server are correct. |
After every edit, reload or restart only after checking the file syntax and preserve a way back to the VPS console. A bad authentication rule can lock out remote clients, while a bad listener setting can prevent expected connections entirely.
Back up and maintain the VPS database
Backups are part of creating a usable database, not an optional final task. Ubuntu’s guidance says PostgreSQL databases should be backed up regularly and points administrators to the PostgreSQL Administrator’s Guide for backup approaches.
Logical backups
A logical backup such as pg_dump exports one database’s schema and data and is useful when you need database-level restore granularity or migration between compatible PostgreSQL environments. A cluster-wide role and database definition can be captured with pg_dumpall. Schedule the job outside peak load, protect the output, and store a copy away from the VPS. Treat an unencrypted dump as containing the same sensitive data as the live database.
Physical or managed backups
Physical backup methods copy the database storage for broader disaster recovery and can be preferable when the workload requires faster recovery of an entire cluster. A managed backup service can reduce operational work, but you must still verify what it covers, how long it retains copies, and whether it can restore the PostgreSQL version and configuration you run.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →| Backup approach | Restore scope | Operational overhead | Important validation |
|---|---|---|---|
Logical (pg_dump, pg_dumpall) |
Selected database, schema, data, and cluster definitions as exported | Usually straightforward to schedule and move | Run test restores and confirm extensions, roles, and permissions |
| Physical | Whole cluster or storage-level recovery | More storage and recovery planning | Test recovery on a separate system and measure recovery time |
| Managed backup | Depends on the provider’s service and plan | Less infrastructure to operate, but external dependency | Read retention and restore guarantees; perform independent restore tests |
Keep backups off the VPS and periodically restore one to an isolated environment. A backup that has never been restored is an assumption, not a recovery plan.
Security checklist for a Linux VPS
- Use a dedicated application role rather than the
postgressuperuser. - Give the role a strong, unique password and keep it outside source code and shell history.
- Leave local
peerauthentication in place when its OS-to-role behavior is useful; use TCP explicitly when testing password authentication. - Use
scram-sha-256for password-based host rules on current Ubuntu installations. - Prefer
hostsslwith a certificate trusted by the client environment for production remote connections. - Restrict
listen_addressesto the interfaces that need PostgreSQL. - Allow TCP 5432 only from known application or administrator addresses at every firewall layer.
- Place specific
pg_hba.confrules before broad rules because PostgreSQL stops at the first match. - Never use
truston an Internet-facing VPS. - Schedule backups, store copies off the VPS, and test restoration.
After the database is created
Give the application its connection settings separately from administrative credentials: host, port 5432, database name appdb, user appuser, password, and whether TLS is required. Keep the database owner and administrative account out of application configuration. Create application tables and additional privileges through a controlled migration process, then monitor logs and storage so that a full disk or repeated authentication failures do not become an outage.
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.
