求助：将post.dmp转成.sql并导入PostgreSQL的操作方法

Hey there! Let's break down how to handle your post.dmp file for PostgreSQL, both importing it directly and converting it to a .sql file. Here's a step-by-step guide tailored to your needs:

直接导入post.dmp到PostgreSQL #

First, let's clarify: most .dmp files for PostgreSQL are created with pg_dump (PostgreSQL's native backup tool), which uses a custom compressed format. Here's how to import it:

1. Ensure PostgreSQL is running
   On Linux, you can check/start the service with:

   sudo systemctl start postgresql
   

   On Windows, open Services (search for "Services" in the Start menu) and make sure the "PostgreSQL" service is set to "Running".

2. Create a target database (if you don't have one yet)
   Replace your_username with your PostgreSQL username and target_db with the name you want for your database:

   createdb -U your_username target_db
   

3. Import using pg_restore (for custom-format dumps)
   This is the standard tool for importing pg_dump-generated .dmp files:

   pg_restore -U your_username -d target_db -v post.dmp
   

   • -U: Specifies your PostgreSQL username
   • -d: The target database you just created
   • -v: Enables verbose mode so you can see the import progress
     If your dump is large, add -j 4 (replace 4 with the number of CPU cores you want to use) to speed up the import with parallel processing.

   Note: If your .dmp is actually a plain-text SQL dump (less common, but possible), use psql instead:

   psql -U your_username -d target_db -f post.dmp
   

将.dmp转换为.sql文件 #

If you need a human-readable .sql file (either for editing or importing via psql), here's how to convert it:

For pg_dump custom-format dumps #

Use pg_restore to export the dump to a SQL file:

pg_restore -U your_username -f converted_post.sql post.dmp

This will generate converted_post.sql, which you can then import into PostgreSQL using:

psql -U your_username -d target_db -f converted_post.sql

For non-PostgreSQL dumps (e.g., Oracle) #

If your post.dmp comes from another database like Oracle, you'll need to use that database's tools first. For example, Oracle's impdp can export the dump to a SQL file, which you can then adjust for PostgreSQL syntax (since there are differences in data types, functions, etc.).

一些关键注意事项 #

• Permissions: Make sure your PostgreSQL user has the necessary permissions to create databases, tables, and insert data. If you get permission errors, try using the superuser (postgres by default).
• Avoid data loss: If your target database already has data, consider backing it up first before importing the dump.
• Auto-create database: If your dump includes the database creation statement, you can skip step 2 above and use:

  pg_restore -U your_username -C -d postgres post.dmp
  

  This connects to the default postgres database and creates the target database automatically from the dump.

内容的提问来源于stack exchange，提问作者chandu