Export Db with Postgresql's Pgadmin-Iii

Export Postgresql table data using pgAdmin

Just right click on a table and select "backup". The popup will show various options, including "Format", select "plain" and you get plain SQL.

pgAdmin is just using pg_dump to create the dump, also when you want plain SQL.

It uses something like this:

pg_dump --user user --password --format=plain --table=tablename --inserts --attribute-inserts etc.

Export DB with PostgreSQL's PgAdmin-III

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.

Export and import table dump (.sql) using pgAdmin

1. In pgAdmin, select the required target schema in object tree (databases ->your_db_name -> schemas -> your_target_schema)
2. Click on Plugins/PSQL Console (in top-bar)
3. Write \i /path/to/yourfile.sql
4. Press enter

Postgresql, export a sql file to create database

You export only schema without data using pg_dump in psql [terminal]:

pg_dump -U postgres -s databasename > backup.sql

or with Pgadmin4

1. Go to Tool -> Backup and select only schema in the Dump option.

Export a CREATE script for a database

To generate a sql script that will create the tables as they exist in a given database do:

pg_dump --schema-only --no-owner the_database > create_the_tables.sql

This will give you a bunch of create table statements. Just to see how portable it was I tried the above as follows:

bvm$ pg_dump -s --no-owner devdb | sqlite3 so_ans.db

And then:

bvm$ sqlite3 so_ans.db .schema
CREATE TABLE courses (
    id integer NOT NULL,
    name text,
    created_by integer,
    jc text
);

Kind of cool.

Export sql script with data from postgres database with pgAdmin

The --file specification in the command line identifies the name and path to the dump file that is created.

---

Related Topics