From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Should I add a new schema for my application? |
Date: | 2023-04-15 10:06:21 |
Message-ID: | CAADeyWg7SB37GshGHgZS1xZLKtYuZ7K67RW3dNEP=PBC369f-g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2023-04-15 10:46:59 | Re: JSON / ASP.NET AJAX Dates support in PostgreSQL |
Previous Message | Federico | 2023-04-15 06:54:36 | Re: Guidance on INSERT RETURNING order |