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