Deploying PostgreSQL

PostgreSQL (often referred to as Postgres) is a powerful, open-source object-relational database system. It is widely admired for its reliability, robust feature set, and adherence to SQL standards. It is the go-to choice for applications requiring complex queries, ACID compliance, and strict data integrity.

Zeabur makes managing relational databases effortless. With Zeabur, you can launch a production-ready PostgreSQL instance in seconds without dealing with complex configuration files, storage volumes, or server maintenance.

What you will learn

In this tutorial, we will guide you through:

1. Deploying a PostgreSQL service from the Zeabur Marketplace.
2. Connecting to your database using external tools like TablePlus, pgAdmin, or psql.
3. Connecting your deployed backend application to PostgreSQL.

---

Phase 1: Deploying the Database

Step 1: Create the PostgreSQL Service

Zeabur offers a "one-click deployment", meaning you rarely need to configure complex settings manually.

Option 1: Create Postgresql instance from Project page

1. Log in to your Zeabur Dashboard.
2. Click the "New Project" button.
3. Select "Template" (Marketplace).
4. Search for PostgreSQL.
5. Select the official PostgreSQL image. Your service will start deploying immediately.

Option 2: Create Postgresql instance from template page

1. Log in to your Zeabur Dashboard.
2. Select Features and Template.
3. Choose or Search PostgreSQL.
4. Select PostgreSQL (usually the official image) click deploy and it will start deploying.

Step 2: Get Your Connection Credentials

1. Go to the Instruction tab of your service.
2. Look for the "Connection" section. You will see two types of info:
   • Public (External): Used for VS Code / Local App.
   • Private (Internal): Used for your deployed App.
3. Note down the following details from the Public section:
   • Host: (e.g., postgres.zeabur.app)
   • Port: (Usually 5432 or a randomized port)
   • Username: (Default is usually root or postgres)
   • Password: (Click the "Eye" icon to reveal)
   • Database: (Default is postgres)

---

Phase 2: Connecting with database

Prerequisite: Get Your Public Connection Info

Before you open any tool, ensure you have your connection details ready from the Zeabur Dashboard.

1. Go to your PostgreSQL Service in Zeabur.
2. Click the Instruction tab.
3. Look at the "Public" (External) section. You will get:
   • Connection String
   • PostgreSQL Connect Command
   • PostgreSQL username
   • PostgresSQL password
   • PostgresSQL database
   • PostgreSQL host
   • PostgreSQL port

---

Option 1: Connect using VS Code (SQLTools)

Best for: Developers who want to write code and check the database in the same window.

Step 1: Install Extensions

1. Open VS Code or Any VS Code alternatives.
2. Click the Extensions icon in the left sidebar (Ctrl+Shift+X or Cmd+Shift+X).
3. Search for and install: SQLTools.
4. Search for and install the driver: SQLTools PostgreSQL/Cockroach Driver.

Step 2: Create Connection

1. Click the SQLTools icon (a database cylinder) that appeared in your left sidebar.
2. Hover over "Connections" and click the "Add New Connection" icon (a cylinder with a +).
3. Select PostgreSQL.

Step 3: Configure Settings Fill in the form using your Zeabur details:

• Connection Name: Zeabur DB (or any name you like)
• Connect using: Server and Port
• Server Address (Host): Paste your Postgresql Host.
• Port: Paste your Postgresql Port.
• Database: Paste your Postgresql Database (usually postgres).
• Username: Paste your Postgresql User.
• Password: Paste your Postgresql Password.
• SSL: Leave as default (or select "Require" if connection fails).

Step 4: Test and Connect

1. Click "Test Connection" at the bottom. You should see a green "Successfully Connected" message.
2. Click "Save Connection".
3. In the sidebar, right-click your new connection and select "Connect".
4. You can now expand the tree view to see your Tables, Views, and Functions.

---

Option 2: Connect using pgAdmin 4 (Official GUI)

Best for: Comprehensive database management using the standard, open-source interface used by database administrators.

Step 1: Install pgAdmin

1. Download and install pgAdmin 4 for your OS (Windows, macOS, or Linux).
2. Open pgAdmin 4. (Note: It may open in your web browser, which is normal).

Step 2: Register Server

1. In the left sidebar (Browser tree), right-click on Servers.
2. Select Register > Server...

Step 3: Enter Credentials A pop-up window will appear. You need to fill in two tabs:

• Tab 1: General
  • Name: Enter a name for your project (e.g., Zeabur App DB).
• Tab 2: Connection
  • Host name/address: Paste your Postgresql Host (e.g., xxxx.clusters.zeabur.com).
  • Port: Paste your Postgresql Port.
  • Maintenance database: Paste your Postgresql Database (usually postgres).
  • Username: Paste your Postgresql User (usually root).
  • Password: Paste your Postgresql Password.
  • Optional: Click "Save Password" to avoid typing it every time.
• Tab 3: Parameters (Optional)
  • If you encounter SSL errors, go to the SSL tab and set "SSL mode" to Require or Prefer.

Step 4: Connect

1. Click the blue Save button.
2. Your database will appear in the sidebar list.
3. Click the arrow > next to your server name > Databases > postgres > Schemas > public > Tables to view your data.

---

Phase 3: Connecting Your App (Inter-Service)

When you deploy your application (the one you made in AI Studio/GitHub) to Zeabur, it needs to talk to this database. You should use Private Networking for better security and speed.

Method A: The Easy Way (Environment Variables)

Zeabur automatically exposes connection strings to other services in the same project. In your App Service settings:

1. Go to the Variables tab.

2. Add a new variable named DATABASE_URL (or whatever your code uses).

3. For the value, use the Zeabur reference variable:This automatically pulls the secure, internal connection string.

   ${POSTGRES_CONNECTION_STRING}
   
   

Method B: The Manual Way (Specific Variables)

If you need specific parts of the connection (or have multiple DBs), use these variables:

• Host: ${POSTGRES_HOST} (Your DB hostname, usually ending in .zeabur.internal)
• Port: ${POSTGRES_PORT}
• User: ${POSTGRES_USERNAME}
• Password: ${POSTGRES_PASSWORD}

Note: If your project contains multiple PostgreSQL instances, reference variables might be unreliable. In that case, manually copy the "Internal" Hostname and Port from the Networking tab.

---

Phase 4: Maintenance & Backups

Configuration (Advanced)

If you need to tweak specific database settings (like memory limits or logging):

1. Open the Config Editor in Zeabur.
2. Edit the file /etc/postgresql/postgresql.conf.
3. Note: Zeabur pre-configures essential settings, so this is rarely needed for beginners.

Restoring a Backup

Zeabur performs automatic backups. To restore one:

1. Download: Download and extract the backup file from Zeabur's backup storage. You will get a data.sql file.

2. Prepare: Ensure you have PostgreSQL client tools installed on your local machine.

3. Run Command: Find your "PostgreSQL Connect Command" in the Instructions tab and run this in your terminal:Example: psql -h postgres.zeabur.app -p 5432 -U root postgres < data.sql

   <Your PostgreSQL Connect Command> < data.sql
   
   

Warning: Restoring a backup replaces the existing data and user password. If you want to keep the current password, remove the CREATE ROLE and ALTER ROLE statements from the data.sql file before running the command.