So you have a layer in QGIS that you want to export to PostgreSQL/PostGIS as a new database?
Create a new Database in Postgres
- In your preferred database GUI (I use Dbeaver Community Edition), create the database.
- Add the PostGIS extension! Execute the following:
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
Create the Postgres Connection in QGIS
Here are the steps:
- In QGIS, locate the Browser panel
- Right-click on PostGIS and select New Connection…
- Complete the values for
- Name e.g. fnweb31_omaps
- Host e.g. 192.168.1.31
- Database as created above e.g. omaps
- (Optionally, turn on Allow saving/loading QGIS projects in the database)
- Click Test Connection to verify it works
- Enter credentials for Postgres
- A message appears at the top of the dialog detailing whether the connection worked or not.
- Click OK
There is a new connection on the expanded PostGIS branch of the Browser panel. Let’s keep going.
Open the DB Manager
- Go to menu Database > DB Manager
- Expand the PostGIS branch
- Select the newly created fnweb31_omaps connection
- Click the Import Layer/File… button in the ribbon
- Change the Input dropdown to the layer you wish to import. The list of currently opened layers is pre-filled, or you can browse to another file.
- Set the Schema to public unless you have other reasons for changing it
- Set the table name to the desired name – it is prefilled with the existing name in QGIS or you can change it
- Click OK
- Re-enter credentials if required
- Close the DB Manager dialog
Now we have a geographic layer stored in Postgres that can be queried, edited from anywhere and by any number of people. It’s fast, robust, and how things should be.