PostgreSQL Database Management

ServBay provides a built-in Adminer tool to facilitate the management of PostgreSQL databases. You can access this tool through the following link:

• Adminer: https://servbay.host/adminer.php (opens new window)

Here are detailed steps for managing PostgreSQL databases using Adminer:

Obtain superuser Password for the Database

1. Open the ServBay interface.

2. Click on "Settings" in the left navigation bar.

3. In the settings page, find the "Database Password" option.

4. Copy the corresponding database superuser username and password.

Using Adminer

1. Open your browser and go to Adminer (opens new window).

2. Input your PostgreSQL database superuser username and the password obtained from ServBay, then click login.

3. Within the Adminer interface, perform operations like:

   • Create a Database:

     • Click "SQL Command" in the left sidebar.
     • Input SQL commands to create a new database, for example:

       CREATE DATABASE your_database_name;
       

       Then click "Execute."

   • Create Tables:

     • Within the chosen database, click "Add Table."
     • Input the new table's name and fields, then click "Save."

   • Import Data:

     • In the chosen database, click "Import."
     • Choose the SQL file to import and click "Import."

   • Other Operations: The left sidebar allows additional actions like modifying table structure, querying data, and deleting records.

Following these detailed steps, you can conveniently manage PostgreSQL databases using Adminer.

Using Command-Line Management

For those who prefer command-line database management, install ServBay's command-line tools. Refer to the Command-Line Support documentation for detailed installation steps.

Once the command-line tools are installed, use the following command to connect to the PostgreSQL database:

# Connect to the local PostgreSQL database
psql -U <superuser> -d postgres

Replace <superuser> with the superuser username obtained, for example, servbay_root.

Common PostgreSQL Operations

Here are some common PostgreSQL database operations:

• List all databases:

  \l
  

• Select a database:

  \c database_name
  

• View all tables in a specified database:

  \dt
  

• View the structure of a specified table:

  \d table_name
  

• Create a user:

  CREATE USER your_user WITH PASSWORD 'your_password';
  

• Grant user privileges:

  ALTER USER your_user WITH SUPERUSER;
  

• Create a table:

  CREATE TABLE your_table_name (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255)
  );
  

• Insert data:

  INSERT INTO your_table_name (name) VALUES ('John Doe');
  

• Query data:

  SELECT * FROM your_table_name;
  

• Update data:

  UPDATE your_table_name SET name = 'Jane Doe' WHERE id = 1;
  

• Delete data:

  DELETE FROM your_table_name WHERE id = 1;