PostgreSQL :- Sample Database – northwind on Windows

Background

Wanted to get understanding of a couple of queries on PostgreSQL database.

Download

Downloaded and Installed on a MS Windows Machine.

Sample Databases

Googled for Sample databases.

Northwind database

Northwind database seems to be most familiar to me.

Artifacts

pthom

user pthom has a working version available on GitHub.

Repository

The Repository is https://github.com/pthom/northwind_psql ( Link )

Files

  1. northwind.sql ( https://github.com/pthom/northwind_psql/blob/master/northwind.sql )
  2. create_db.sh ( https://github.com/pthom/northwind_psql/blob/master/create_db.sh )

northwind.sql

Creates the database objects and populates the tables with data.

Create_db.sh

Create_db.sh is a shell script.

Let us  modify it a bit to work as a MS-DOS batch file.

_base.cmd
Outline

Set environment variables.

  1. Database
    • Core
      • Server Name
      • Server Port
      • System Database
      • Username
      • User Password
    • Application
      • App Database
      • App User
      • App User Password
    • PGPASSWORD
      • Environment Variable used by PostgreSQL for User Password
Code

SET _server=localhost

SET _database=postgres

SET _databaseApp=northwind

SET _port=5432

SET _username=postgres

SET _password=postgresUSERPASSSWORD

set PGPASSWORD=%_password% 

invoke.cmd
Outline
  1. PostgreSQL client ( psql )
    • Set folder
    • Set filename
  2. Set filename for Database Creation Script ( northwind.sql )
  3. Set Command Line Parameter ( _cmdLineParm )
  4. Disallow new connection to app database
    • UPDATE pg_database SET datallowconn = ‘false’ WHERE datname={database}
  5. Terminate existing connections on app database
    • SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = ‘{database}’
  6. Drop app database ( northwind )
  7. Drop app user ( northwind_user )
  8. Create app database ( northwind )
  9. Populate app database ( northwind )
  10. Create app user { create user => northwind_user }
  11. Change app user password { alter user }
  12. Grant all permissions on app database to app user
Code
setlocal

call _base.cmd

set "_binFolder=C:\Program Files\PostgreSQL\11\bin\"

set "_fileDBSample=northwind.sql"

set "_cmdLineParm=-h %_server% -U %_username% -d %_database% -p %_port%"

"%_binFolder%\psql" %_cmdLineParm% -c "UPDATE pg_database SET datallowconn = 'false' WHERE datname = '%_databaseApp%';"

"%_binFolder%\psql" %_cmdLineParm% -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '%_databaseApp%';"

"%_binFolder%\psql" %_cmdLineParm% -c "DROP DATABASE if exists %_databaseApp% "

"%_binFolder%\psql" %_cmdLineParm% -c "DROP USER if exists northwind_user"

"%_binFolder%\psql" %_cmdLineParm% -c "create database %_databaseApp% "

set "_cmdLineParm=-h %_server% -U %_username% -d %_databaseApp% -p %_port%"

"%_binFolder%\psql" %_cmdLineParm% --quiet -f %_fileDBSample%

"%_binFolder%\psql" %_cmdLineParm%  -c "create user northwind_user;"

"%_binFolder%\psql" %_cmdLineParm%  -c "alter user northwind_user password 'thewindisblowing';"

"%_binFolder%\psql" %_cmdLineParm%  -c "grant all on DATABASE %_databaseApp% to northwind_user;"

"%_binFolder%\psql" %_cmdLineParm%  -c "GRANT ALL on ALL tables IN SCHEMA public to northwind_user"

endlocal

Review

pgAdmin

To quickly review, please launch pgAdmin and list the tables and browse loaded data.

pgAdmin

outline.01.20190726.0402PM

 

Referenced Work

pthom

pthom/northwind_psql
Link

References

  1. PostgreSQL
    • Disallow new connections
      Link
    • Terminate existing connections
      Link
    • Postgresql – is there a way to disable the display of INSERT statements when reading in from a file? [closed]
      Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s