[GIS] export postgis database

postgis

I'm totally new to the awesome PostGIS world, so sorry for the maybe silly question.

After a while I was able to install postgres and postgis on my computer (debian testing) and I was also able to create some databases.

Through the DB Manager of QGIS I have imported some shapefile into the database and that's ok.

But now, if I want to export the entire postgis db as a single file (like SpatiaLite does) how can I do that?
I mean, Spatialite creates a real file into a directory while postgis no (or at least I haven't found it).

Thanks guys and sorry for the simple question

Best Answer

Have a look at Postgres Schemas (which are very easy to use)

It is generally a good practice to use a different schema from the default public schema. The reason is that when you export the database, you get all the PostGis objects, functions, tables, etc which are installed there.

What I do is that I create a schema in the database, say "data"

create schema data;

I then set that schema to be the default by using the 

alter database "mydb" set search_path=data,public;

It is not absolutely necessary to set the search path, you could user the reference to the table as schema.table

data.mytable

in all your table names, but the using the default means you can move data between schema's and one simple SQL command switches between them, great for upgrading production data, etc

When you export the database, just export that particular schema using pg_dump with

--schema=schemaName

If you have a bunch of tables to move between schema's, you can use the following SQL

create schema data;
alter database "**DBNAME**" set search_path=data,public;
DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' -- and other conditions, if needed
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA data;';
    END LOOP;
END;
$$;

When you restore into another database, restore it to a schema other than public, in this way you will never get any conflicts with objects in the public schema and it makes it so easy to just backup and move your spatial data around

Related Question