8

How to export a Postgresql db into SQL that can be executed into other pgAdmin?

  • Exporting as backup file, doesn't work when there's a difference in version
  • Exporting as SQL file, does not execute when tried to run on a different pgAdmin

I tried exporting a DB with pgAdmin III but when I tried to execute the SQL in other pgAdmin it throws error in the SQL, when I tried to "restore" a Backup file, it says there's a difference in version that it can't do the import/restore.

So is there a "safe" way to export a DB into standard SQL that can be executed plainly in pgAdmin SQL editor, regardless of which version it is?

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
quarks
  • 33,478
  • 73
  • 290
  • 513
  • So I guess you tried something like described in http://stackoverflow.com/questions/4599696/use-pg-restore-to-restore-from-a-newer-version-of-postgresql ? – Jerska May 28 '13 at 05:50
  • Yes, I've tried using the 'psql' however nothing happens, no error, but the DB is not updated with the tables ,etc. – quarks May 28 '13 at 05:57
  • So I am thinking of a more generic approach, a SQL backup that can be executed in any version of postgreSQL – quarks May 28 '13 at 05:57
  • How do you try to run SQL dump in pgAdmin? You copy-paste it into SQL console? what exactly error do you have? do you try to export/import content of some system tables or sth? – Danubian Sailor May 28 '13 at 08:01
  • 1
    "throws an error" should never appear in your question. **What error?**. What PgAdmin-III version and PostgreSQL version? When you say it "doesn't work" when there's a different version, how **exactly* does it not work? – Craig Ringer May 28 '13 at 08:30
  • What an awful tool in this case. In phpMyadmin, the creation of a dump is really intuitive, people don't need to go on StackOverflow and even after that having problems! – fresko Feb 19 '19 at 13:53

2 Answers2

8

Don't try to use PgAdmin-III for this. Use pg_dump and pg_restore directly if possible.

Use the version of pg_dump from the destination server to dump the origin server. So if you're going from (say) 8.4 to 9.2, you'd use 9.2's pg_dump to create a dump. If you create a -Fc custom format dump (recommended) you can use pg_restore to apply it to the new database server. If you made a regular SQL dump you can apply it with psql.

See the manual on upgrading your PostgreSQL cluster.

Now, if you're trying to downgrade, that's a whole separate mess.

You'll have a hard time creating an SQL dump that'll work in any version of PostgreSQL. Say you created a VIEW that uses a WITH query. That won't work when restored to PostgreSQL 8.3 because it didn't support WITH. There are tons of other examples. If you must support old PostgreSQL versions, do your development on the oldest version you still support and then export dumps of it for newer versions to load. You cannot sanely develop on a new version and export for old versions, it won't work well if at all.

More troubling, developing on an old version won't always give you code that works on the new version either. Occasionally new keywords are added where support for new specification features are introduced. Sometimes issues are fixed in ways that affect user code. For example, if you were to develop on the (ancient and unsupported) 8.2, you'd have lots of problems with implicit casts to text on 8.3 and above.

Your best bet is to test on all supported versions. Consider setting up automated testing using something like Jenkins CI. Yes, that's a pain, but it's the price for software that improves over time. If Pg maintained perfect backward and forward compatibility it'd never improve.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
3

Export/Import with pg_dump and psql

1.Set PGPASSWORD

export PGPASSWORD='123123123';

2.Export DB with pg_dump

pg_dump -h <<host>> -U <<username>> <<dbname>> > /opt/db.out

/opt/db.out is dump path. You can specify of your own.

3.Then set again PGPASSWORD of you another host. If host is same or password is same then this is not required.

4.Import db at your another host

psql -h <<host>> -U <<username>> -d <<dbname>> -f /opt/db.out

If username is different then find and replace with your local username in db.out file. And make sure on username is replaced and not data.

If you still want to use PGAdmin then see procedure below.

Export DB with PGAdmin:

Select DB and click Export.

  1. File Options
    • Name DB file name for you local directory
    • Select Format - Plain
  2. Ignore Dump Options #1
  3. Dump Options #2
    • Check Use Insert Commands
  4. Objects
    • Uncheck tables if you don't want any

Import DB with PGAdmin:

  1. Create New DB.
  2. By keeping selected DB, Click Menu->Plugins->PSQL Console

  3. Type following command to import DB

    \i /path/to/db.sql

If you want to export Schema and Data separately.

Export Schema

  1. File Options
    • Name schema file at you local directory
    • Select Format - Plain
  2. Dump Options #1
    • Check Only Schema
    • Check Blobs (By default checked)

Export Data

  1. File Options
    • Name data file at you local directory
    • Select Format - Plain
  2. Dump Options #1
    • Check Only Data
    • Check Blobs (By default checked)
  3. Dump Options #2
    • Check Use Insert Commands
    • Check Verbose messages (By default checked)

Note: It takes time to Export/Import based on DB size and with PGAdmin it will add some more time.

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226