반응형

/********************************************************************************************
-- Title : [PGS9.2] PostgreSQL DVDRental Sample Database
-- Reference : postgresqltutorial.com
-- Key word : postgresql sample db sampledb sample database 샘플 디비 샘플디비 tttdb copy
********************************************************************************************/

-- Sample Database Description for PostgreSQL
    o References : postgresqltutorial.com
    o Customizing - Change database name dvdrental to tttdb and add some schemas.
                          - Change some naming rules like this'customer_customer_id_seq' to 'seq_customer_customer_id'
                          - Make shell script and run it to install database using provided each .sql files.
                          - Make some tablespaces and use these when create tables and indexes.
    o Please add to a comment if you have any license issues.


-- Table List
select * from sch_customer.address limit 100;
select * from sch_customer.city limit 100;
select * from sch_customer.country limit 100;
select * from sch_customer.customer limit 100;
select * from sch_film.actor limit 100;
select * from sch_film.category limit 100;
select * from sch_film.film limit 100;
select * from sch_film.film_actor limit 100;
select * from sch_film.film_category limit 100;
select * from sch_film.inventory limit 100;
select * from sch_film.language limit 100;
select * from sch_shop.payment limit 100;
select * from sch_shop.rental limit 100;
select * from sch_shop.staff limit 100;
select * from sch_shop.store limit 100;

-- ERD



-- CreateSampleDB.sh

#!/bin/bash


## Set parameters

PGCTL=/home/postgres/data/bin/pg_ctl

INSTALL=/home/SampleDB


export PGCTL INSTALL



## Check Password


echo -ne "Type password for pgsys : " 

read -s pwd1

echo -ne "\nRetype password : "

read -s pwd2


if [ $pwd1 != $pwd2 ]

then 

    echo

    echo "*** What the fu**! Passwords do not match! ***"

    echo "*** Finished this script. ***"

    echo "*** Try again. ***"

    exit 0

fi


## Initiate Environment

echo ""

echo "*** Initiate Environment ***************"

su - postgres -c "psql postgres -f \"$INSTALL/init.sql\"  "

rm -rf /home/database



## Make Tablespace Directory

echo "*** Make Tablespace Directory **********"

mkdir /home/database

mkdir /home/database/ARCHIVE

mkdir /home/database/tbs_data0

mkdir /home/database/tbs_data1

mkdir /home/database/tbs_index0



## Change Owner to postgres.postgres 

echo "*** Change Owner of /home/database *****"

chown -R postgres.postgres /home/database



## Create Superuser

echo "*** Create Super User ******************"

su - postgres -c "psql -c \"create role pgsys login password '$pwd1' superuser;\"  "



## Create Tablespace

echo "*** Create Tablespace ******************"

su - postgres -c "psql -f \"$INSTALL/tablespace.sql\"  "



## Create User and Group

echo "*** Create User and Group **************"

su - postgres -c "psql -f \"$INSTALL/usergroup.sql\"  "



## Create Database

echo "*** Create Database ********************"

su - postgres -c "psql -f \"$INSTALL/database.sql\"  "



## Create Schema on tttdb

echo "*** Create Schema on tttdb *************"

su - postgres -c "psql tttdb -f \"$INSTALL/schema.sql\"  "



## Create Sequence on tttdb

echo "*** Create Sequence on tttdb ***********"

su - postgres -c "psql tttdb -f \"$INSTALL/sequence.sql\"  "



## Create Grant on tttdb

echo "*** Create Grant on tttdb *************"

su - postgres -c "psql tttdb -f \"$INSTALL/grant.sql\"  "



## Create Type and Domain on tttdb

echo "*** Create Type and Domain on tttdb ****"

su - postgres -c "psql tttdb -f \"$INSTALL/typedomain.sql\"  "



## Create Table on tttdb

echo "*** Create Table on tttdb **************"

su - postgres -c "psql tttdb -f \"$INSTALL/table.sql\"  "



## Create Function

echo "*** Create Function on tttdb ***********"

su - postgres -c "psql tttdb -f \"$INSTALL/function.sql\"  "



## Create View 

echo "*** Create view  on tttdb **************"

su - postgres -c "psql tttdb -f \"$INSTALL/view.sql\"  "



## Copy data on tttdb

echo "*** Add Relation on tttdb **************"

su - postgres -c "psql tttdb -f \"$INSTALL/copydata.sql\"  "



## Add Relation on tttdb

echo "*** Add Relation on tttdb **************"

su - postgres -c "psql tttdb -f \"$INSTALL/relation.sql\"  "



## Create Index on tttdb

echo "*** Create Index on tttdb **************"

su - postgres -c "psql tttdb -f \"$INSTALL/index.sql\"  "



## Create Trigger on tttdb

echo "*** Create Trigger on tttdb **************"

su - postgres -c "psql tttdb -f \"$INSTALL/trigger.sql\"  "

 

-- SampleDB Tar File



-- SampleDB Each Files



 
반응형

+ Recent posts