Search Postgresql Archives

Should I add a new schema for my application?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux