# Importing and exporting PostgreSQL databases

> Export and import PostgreSQL databases with pg_dump, psql or phpPgAdmin for backups, migrations and restores.

Source: https://helpcenter.ruachost.com/developers/databases/importing-and-exporting-postgresql-databases/

> **PostgreSQL is available on Managed VPS only:**
Ruachost shared hosting supports MySQL (MariaDB) databases, not PostgreSQL. To use PostgreSQL, choose a [Managed VPS](https://ruachost.com/vps).

PostgreSQL provides built‑in tools for backing up and restoring databases. On **ruachost.com**, you can use command‑line utilities or phpPgAdmin to import and export PostgreSQL databases for migration, backup, or recovery purposes.

## Why Import/Export Databases?

- Transfer a database between hosting accounts or providers.
- Back up data for disaster recovery.
- Restore a database from a previous backup.
- Share a database with developers or third‑party applications.

## Exporting a PostgreSQL Database

### Method 1: Using `pg_dump`

1. Log in to your hosting account via SSH.
2. Run the following command:

   ```bash
   pg_dump -U dbusername dbname > dbexport.pgsql
   ```

   - Replace `dbusername` with your PostgreSQL username.
   - Replace `dbname` with the name of the database.
   - The file `dbexport.pgsql` will contain all database data.

???? **Note:** If you encounter permission errors with PostGIS templates, use:

```bash
pg_dump -U dbusername dbname -N topology -T spatial_ref_sys > dbexport.pgsql
```

### Method 2: Using phpPgAdmin

1. Log in to **cPanel**.
2. Go to **Databases → phpPgAdmin**.
3. Select the database.
4. Click **Export**.
5. Choose **Structure and data** → Format: **SQL** → Option: **Download**.
6. Save the file locally.

## Importing a PostgreSQL Database

### Step 1: Create a New Database

1. Log in to **cPanel**.
2. Go to **Databases → PostgreSQL Databases**.
3. Enter a name for the new database.
4. Assign a user to the database.

### Step 2: Import Data

**Method 1: Using** `psql`

1. Transfer the backup file (`dbexport.pgsql`) to your hosting account via SCP, SFTP, or FTP.
2. Log in via SSH.
3. Run:

   ```bash
   pg_dump -U dbusername dbname > dbexport.pgsql
   ```

### Method 2: Using phpPgAdmin

1. Log in to **cPanel → phpPgAdmin**.
2. Select the target database.
3. Click **SQL**.
4. Upload the backup file.
5. Click **Execute** to import data.

> **Important Notes:**
- Always back up your database before importing new data.
- Use the **primary PostgreSQL user** for imports to ensure full access.
- Clear application cache after restoring databases to avoid inconsistencies.
- Large imports may require increasing PHP execution time or memory limits.