-- 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
-- ERD
#!/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