How to import multiple postgres databases using single file in another ubuntu installation

Is there a way to move all databases from Pgadmin 3 one ubuntu installation to another.

I have created a big dump (a single file to export all database from one system). Now I want to import that big file in my pgadmin3 or in console in postgres.

Is there a way to get this accomplished? Both installation has same versions and OS numbers.

Both has Ubuntu 16.04 LTS with Postgres 9.5 installed.

Any better way to get that done please?

1 Answer

The usual way to do what you ask is to run as the database superuser (postgresql)

 pg_dumpall >file 

from one postgresql cluster (all the databases on a system) into a file, then simply import the file with

psql \i file

or

psql -f file postgresql

Read the manual for pg_dumpall

man pg_dumpall 

To change defaults like ports if necessary.

The dump contains all the database, table and user recreations needed, as well as the data itself. Afterward, the manual suggest running

vacuumdb -a -z 

to help out the optimizer.

You can select a single database to dump/backup with pg_dump. You might play around with the -l argument to pg_dumpall to select another database which contains only the databases of interest, but I've never done that. Or just edit the output file with everything and remove what you don't want.