PostGIS (postgis.net) is an extension for the PostgreSQL database. PostgreSQL already has basic geometry types. PostGIS adds additional geospatial types and functions which make handling spatial data in the database easier and more powerful.
In the OSM project PostGIS is used for creating maps with Mapnik; many other people also use PostGIS for other tasks. PostGIS and Mapnik power most of the popular OpenStreetMap maps, including the four main layers on the OSM website.
Note: the PostGIS installation snippets are currently (as of March 2013) spread over many tool specific pages (just google for 'spatial_ref_sys.sql site:wiki.openstreetmap.org'). These snippets possess different levels of completeness and actuality. Bundling the description under the PostGIS wiki page could enable better maintenance due to a bigger user base. Let's create HOWTOs for current software versions and later migrate the installation instructions from all over the wiki.
Following steps will bring you from a freshly installed computer to a PostGIS enabled database with OpenStreetMap data:
1. Install PostgreSQL with PostGIS extension. The procedure depends on OS, OS version and desired Postgres and PostGIS version. Detailed howtos PostGIS/Installation.
2. Creating db user and database(s). Depends on how you are going to access the database (locally or remotely). How many users? Which access rights for different users? PostGIS/Installation#Create database
3. Spatially enabling the database(s) PostGIS/Installation#Activate PostGIS. This step is required for every database you create. You are ready if you are able to use GIS specific functions like
- select postgis_full_version();
- select ST_Point(1, 2) AS MyFirstPoint;
- select ST_SetSRID(ST_Point(-77.036548, 38.895108),4326); -- using WGS 84
With Osmosis, use the --write-pgsql and --write-pgsql-dump command line arguments. The database schema is similar to the database schema in the main database. It contains the following tables: nodes, ways, relations, node_tags, way_tags, relation_tags, way_nodes, relation_members, users. There are no tables for historic data, only for the current data.