Should I add a new schema for my application?

Started by Alexander Farberalmost 3 years ago2 messagesgeneral
Jump to latest
#1Alexander Farber
alexander.farber@gmail.com

Good morning,

I am seeking for a general advice if it would be a good idea for a
PostgreSQL/PostGIS using application to add a third schema.

I am using postgis/postgis Docker image which comes with "public" schema.

Then I have loaded OpenStreetMap data into the database and have created
"osm_schema" for that:

# psql -p 5432 -U postgres

CREATE DATABASE osm_database TEMPLATE=template_postgis;
CREATE USER osm_username WITH ENCRYPTED PASSWORD 'osm_password';
GRANT ALL PRIVILEGES ON DATABASE osm_database TO osm_username; -- TODO
read only

# psql -p 5432 -U osm_username osm_database

CREATE SCHEMA osm_schema AUTHORIZATION osm_username;
ALTER ROLE osm_username SET search_path TO osm_schema, public;

After loading OSM data with the osm2pgsql tool I have 3 tables in there:

# psql -p 5432 -U osm_username osm_database
psql (15.2)

osm_database=> \dt
List of relations
Schema | Name | Type | Owner
------------+--------------------+-------+--------------
osm_schema | planet_osm_line | table | osm_username
osm_schema | planet_osm_point | table | osm_username
osm_schema | planet_osm_polygon | table | osm_username
osm_schema | planet_osm_roads | table | osm_username
public | spatial_ref_sys | table | postgres
(5 rows)

And now I need one more table, for the new mapping feature in my
application, which would reference the osm_id column in the
planet_osm_roads.

I have not used database schemas at all until yet, I am looking for an
advice if I should add a third schema here, just for my application?

Would it be a good long term idea and what advantages/disadvantages would
that bring with it?

Thank you for any feedback and also I have to note, that I am really amazed
by all these software products -

PostgreSQL, PostGIS and also the OpenStreetMap - how smooth the
installation has worked for me - just an hour spent and I have the whole
planet at my fingertips - this is not coming for free, there are probably
decades of effort by many people in the products.

Best regards
Alex

#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Alexander Farber (#1)
Re: Should I add a new schema for my application?

On Sat, 2023-04-15 at 12:06 +0200, Alexander Farber wrote:

I am seeking for a general advice if it would be a good idea for a PostgreSQL/PostGIS using application to add a third schema.

I am using postgis/postgis Docker image which comes with "public" schema.

Then I have loaded OpenStreetMap data into the database and have created "osm_schema" for that:

And now I need one more table, for the new mapping feature in my application, which would reference the osm_id column in the planet_osm_roads.

I have not used database schemas at all until yet, I am looking for an advice if I should add a third schema here, just for my application?

I would add another schema.

The "public" schema for the extensions, "osm_schema" for the OpenStreetMap data,
and a third schema for your table. Perhaps you want to add additional objects later.

Yours,
Laurenz Albe