To dump a PostgreSQL database located at a specific IP address and restore it to a new database, you can follow these steps:

1. Dumping the database:

   • Open a terminal or command prompt on your local machine.

   • Use the pg_dump command-line tool to create a dump of the remote database. Replace <remote_host> with the IP address of the remote server and <database_name> with the name of the database you want to dump.

     shell

     Copy

     pg_dump -h <remote_host> -U <username> -d <database_name> -F c -f <dump_file_name>
     

     Example:

     shell

     Copy

     pg_dump -h 192.168.1.100 -U myusername -d mydatabase -F c -f mydatabase.dump
     

     This command will create a binary dump file named mydatabase.dump containing the data and schema of the remote database.

2. Copy the dump file to your local machine:

   • Use a file transfer method (e.g., SCP, SFTP) to copy the dump file (mydatabase.dump) from the remote server to your local machine.

3. Restoring the database:

   • Create a new, empty database on your local PostgreSQL server where you want to restore the dumped data.

   • Open a terminal or command prompt on your local machine.

   • Use the pg_restore command-line tool to restore the database from the dump file. Replace <new_database_name> with the name of the new database you created in the previous step.

     shell

     Copy

     pg_restore -U <username> -d <new_database_name> -F c <dump_file_name>
     

     Example:

     shell

     Copy

     pg_restore -U myusername -d newdatabase -F c mydatabase.dump
     

     This command will restore the database from the dump file (mydatabase.dump) into the new database (newdatabase) on your local machine.

Make sure you have the necessary permissions and access credentials to connect to the remote database and create databases on your local PostgreSQL server. Adjust the commands according to your specific setup and requirements.

The error message you encountered indicates that the authentication method configured for the "admin" user is set to "peer" authentication, which requires the operating system user to match the PostgreSQL user. However, in your case, it seems that the authentication is failing.

To resolve this issue, you have a few options:

Option 1: Switch to a Trusted User Authentication Method

1. Open the PostgreSQL configuration file (postgresql.conf) on your local machine.
2. Locate the line that begins with #listen_addresses and remove the # to uncomment the line.
3. Change the value after listen_addresses to '*' to allow connections from any IP address.
4. Save the changes and exit the file.
5. Open the pg_hba.conf file, which controls client authentication.
6. Find the line that corresponds to the "admin" user and change the authentication method from peer to trust.
7. Save the changes and exit the file.
8. Restart the PostgreSQL service for the changes to take effect.
9. Retry the pg_restore command.

Option 2: Use a Different Authentication Method

1. Open the pg_hba.conf file.
2. Find the line that corresponds to the "admin" user and change the authentication method from peer to a different method such as md5 or password.
3. Save the changes and exit the file.
4. Restart the PostgreSQL service for the changes to take effect.
5. Retry the pg_restore command.

Note: Using trust or a non-secure authentication method (like md5 or password) is not recommended for production systems. It is advisable to use stronger authentication methods and provide proper credentials for database access.

Remember to exercise caution when modifying PostgreSQL configuration files, as incorrect changes can lead to security vulnerabilities or service disruptions.