# Migrate Database

### Find Settings of existing Database

```
sudo -u postgres psql --cluster <version>/main

-- Database-level info
\l+ your_database

-- Server-wide settings
SHOW SERVER_ENCODING;
SHOW LC_COLLATE;
SHOW LC_CTYPE;

-- Installed extensions
\dx

-- Example Output
Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
----------+----------+----------+------------+------------+--------------------
your_database | appuser | UTF8 | en_US.utf8 | en_US.utf8 |
```

### Export existing Database

```
sudo -u postgres pg_dump -h <server> -p <port> -U <user> -W -Fc -f <output file> <database>

-h  = Hostname
-p  = Port
-U  = Username
-W  = Prompt for Password
-Fc = pg_dump non-Text-Format (required for pg_restore)
-f  = Output File
```

- Command needs to be run with `sudo -u postgres` as the postgres User has (as default) only local Access and only direct Login without any Password.

#### (Optional) Copy to new Server

```
scp <path-to-dump> <user>@<server>:/tmp/<filename.dump>
```

### Create User on new Database

```
sudo -u postgres psql --cluster <version>/main -c "CREATE USER <username> WITH PASSWORD '<password>';"
```

### Create (empty) Database

### Restore Database

```
sudo -u postgres pg_restore -h <server> -p <port> -U <user> -d <database> -c <input_file>

-h  = Hostname
-p  = Port
-U  = Username
-W  = Prompt for Password
-d  = (New) Database Name
-c  = Input File
```