ATOMIC_DUMP_CHANGE_USER

gistfile1.txt

### Document: Steps to Restore PostgreSQL Database with a Different User

#### Step-by-Step Guide

1. **Download the Database Dump:**

   Download the database dump file using `wget`:

   ```bash
   wget https://backup.wecan.dev/wax/atomic/mainnet/atomic.mainnet.1717144441.dump
   ```

2. **Convert the Binary Dump to Plain Text:**

   Use the `pg_restore` command to convert the binary dump file to a plain text format:

   ```bash
   pg_restore -U wecan_user -f plain_text_dump.sql -F c atomic.mainnet.1717144441.dump
   ```

3. **Replace the User in the Plain Text Dump:**

   Use `sed` to replace all instances of `wecan_user` with `aa_user` in the plain text dump file:

   ```bash
   sed -i 's/wecan_user/aa_user/g' plain_text_dump.sql
   ```

4. **Create the New User in PostgreSQL:**

   Connect to your PostgreSQL instance and create the new user (`aa_user`):

   ```bash
   PGPASSWORD=your_password psql -U wecan_user -d postgres
   ```

   Then, run the following SQL command:

   ```sql
   CREATE ROLE aa_user WITH LOGIN PASSWORD 'your_password';
   ```

   Replace `your_password` with the desired password for the new user.

5. **Create the New Database:**

   Create the new database and set the owner to the new user (`aa_user`):

   ```bash
   PGPASSWORD=your_password createdb -U wecan_user "new_database"
   ```

   Replace `new_database` with the name of your new database.

6. **Restore the Edited Plain Text Dump to the New Database:**

   Use the `psql` command to restore the database from the modified plain text dump file:

   ```bash
   PGPASSWORD=your_password psql -U aa_user -d new_database -f /path/to/plain_text_dump.sql
   ```

   Replace:
   - `your_password` with the password for `aa_user`.
   - `new_database` with the name of your new database.
   - `/path/to/plain_text_dump.sql` with the actual path to your modified plain text dump file.

### Complete Command Sequence

```bash
# Step 1: Download the Database Dump
wget https://backup.wecan.dev/wax/atomic/mainnet/atomic.mainnet.1717144441.dump

# Step 2: Convert the Binary Dump to Plain Text
pg_restore -U wecan_user -f plain_text_dump.sql -F c atomic.mainnet.1717144441.dump

# Step 3: Replace the User in the Plain Text Dump
sed -i 's/wecan_user/aa_user/g' plain_text_dump.sql

# Step 4: Create the New User
PGPASSWORD=your_password psql -U wecan_user -d postgres -c "CREATE ROLE aa_user WITH LOGIN PASSWORD 'your_password';"

# Step 5: Create the New Database
PGPASSWORD=your_password createdb -U wecan_user "new_database"

# Step 6: Restore the Edited Plain Text Dump to the New Database
PGPASSWORD=your_password psql -U aa_user -d new_database -f /path/to/plain_text_dump.sql
```

### Notes:
- Ensure that `your_password` is the desired password for the `aa_user`.
- Replace `new_database` with the actual name of the new database you want to create.
- Make sure the path to `plain_text_dump.sql` is correct in the final `psql` command.

By following these steps, you will be able to restore the PostgreSQL database from a binary dump file to a new database with a different user. If you need further assistance, please let me know!