Top 3 Sample Databases for PostgreSQL

If a developer is attempting to learn a new process or test some code, it’s best to utilize a test environment such as a sandbox rather than the production server. A sandbox environment is the place where there are scenarios like the real world but no real world data. It is a secure environment where users can try out various new features, learnings and technology experiments in a safe zone without using or taking up production server space or bandwidth.

It is quite easy to set up sandbox environment but it is not easy to setup a real world scenario in a database. This is when the user needs a sample database. In this article we will go over various sample databases for PostgreSQL databases.

Sample Database 1: Pagila

MySQL has very popular database called Sakila. Lots of people who are moving from MySQL often miss the sample database Sakila. However, Pagila database helps this particular situation where a user is looking for the Sakila database in a PostgreSQL database.

You can download PostgreSQL Sample Database Pagila from here: http://www.postgresqltutorial.com/postgresql-sample-database/

The DVD rental database is created from Sakila sample database of MySQL. The DVD rental database represents business processes of a DVD rental store. Here are few of the objects from DVD rental database:

• 15 tables
• 1 trigger
• 7 views
• 8 functions
• 1 domain
• 13 sequences

Sample Database 2: SportDB

Here is another very interested database which works for MySQL, PostgreSQL and SQL Server. The major purpose of SporsDB is make use of a database schema designed from the top down.

As per the SportsDB’s official documentations:

SportsDB enables streamlined coding techniques, where possible, through adoption of naming standards accessible to many popular model-view-controller publishing software frameworks.

You can download the sample database from here: http://www.sportsdb.org/sd/samples

Sample Database 3: pgFoundry Samples

Most of the database systems provide samples database with their products. If you ever search online for PostgreSQL database there are plenty of databases available online. However the most trusted ones are from pgFoundry Samples. I strongly encourage everyone to try any of the pdFoundry samples if they are interested in working with reliable sample database for PostgresSQL.

Here is the list of the sample database listed on the pgFoundry site.

• World
• Dellstore2
• Pagila
• Book Town
• FreeBase
• DBPedia
• Openstreetmap

You can download all of the above sample databases from the link: https://wiki.postgresql.org/wiki/Sample_Databases

Keyword:

• PostgreSQL sample database
• Postgres sample database
• Sample database for Postgres
• Sample database for PostgreSQL

PostgreSQL vs MySQL and MySQL vs Postgresql performance 2016

PostgreSQL vs MySQL and MySQL vs Postgresql performance 2016

Postgresql Vs Mysql Performance 2016 comparison and also Mysql Vs Postgresql Performance 2016 with latest updated information you can get here. First of all we should know what are Postgresql and Mysql. This PostgreSQL is simply called as Postgres. This is the object relational database management system in shortly, it is called as ORDBMS.

This is very important on extensibility as well as standards observance. As it is the data base server, its primary function to store the data very securely and supporting all the best practices. It will allow the retrieval of the data at the client requests of other software application.

It can handle all the workloads ranging from this single machine application to the large internet facing applications with many of the concurrent users. It includes all the majority of the SQL in 2011 standard. It is the ACID- complaint as well as transactional, which includes most of the DDL commands.

 

 #
 

 #
 


 #
 

# 
 
 #

It also avoids the locking issues using the multi version concurrency control (that is called as MVCC). It also provides more protection and full serializability. It handles all the complex SQL queries using many of the indexing methods which are not available in the other databases. It has all the updatable views as well as materialized views, triggers, supports some functions and also stored procedures. It also supports migration by its extensive standard SQL support and is available with the migration tools.

MySQL is unofficially called as “My Sequel”. It is the open source software that is relational database management system which is shortly called as RDBMS. In the year 2013, this was the world’s second most widely used RDBMS.

This is most widely used as the open source client server model RDBMS. It is named after the co-founder of Michael Widenius’s daughter. This is the Structured Query Language in shortly called as SQL. The development of this project has made its source code very easy and is available under the terms of the GNU that is called as General Public License and also under the variety of proprietary agreements. MySQL was owned as well as sponsored by a single person that is for profit firm only, the Swedish company MySQL AB.

This is now owned by Oracle Corporation. MySQL is the most popular choice of the database for use of the web applications. It is the most central component of the widely used LAMP open source web application software stack. LAMP is the short form for Linux.

The applications that are used by the MySQL databases which includes the TYPO3, Joomla, MODx, WordPress, phpBB, Drupal, MyBB and other softwares. This is also used in many of the high profile as well as large scale websites that include Google, Facebook, Twitter and Flick etc. Let me show more about mysql to postgresql and mysql postgresql 2016.

Postgresql Vs Mysql

PostgreSQL vs MySQL is the decision of many people those are approaching the open source relational database management systems. These both are the time verified solutions in which it competes very strongly with the proprietary database software. MySQL has long that has been assumed to be the faster one but very less full-featured of this two database systems.

This PostgreSQL is assumed to be a more thickly featured database system that is often been described as the open source version of Oracle. MySQL has been very popular when compared to various software projects.

This is because of its speed as well as ease of use while this PostgreSQL has a very close following from the developers, the people those who come from an Oracle or else SQL Server background.

Postgresql Vs Mysql Similarities

Here is only the reasonable basis of comparison between the MySQL as well as PostgreSQL. It is that these both are open source database projects. The similarity is very small between them. MySQL is most distributed under the restrictive “Copyleft” license that is effectively forced to the proprietary software seller to either open source of their application.

In contrast to this PostgreSQL is distributed under the most tolerant terms which are very practical. There is no single or else controlling the commercial unit. The projects are licensed in the terms that are so liberal. It is permitted to create the proprietary profitable copied that is without paying any royalties. As this has already happened, for example, there is one of the cases of EMC’s data warehousing solution that is Greenplum.

Differences in Postgresql Vs Mysql 

In almost all of the records, these projects have radically different outlooks and the each is informed by a completely different set of concern. As traditionally, this PostgreSQL has very much focused on the reliability, data integrity as well as integrated developer that is mainly focused on all the features.

It has very extremely complicated query planner in which it is capable of joining the relatively large numbers of tables in an efficient manner. On the other hand, MySQL has traditionally focused on the read- mostly web applications that are usually written in PHP.

The principal concern is with optimizing of all the simple queries. Considerably, both of these projects have improved a lot and the comparison between these both is very difficult to make it also. The Technical foundations and the mindset of developer those are informed to continue to be a pervasive influence on this both projects.

Advantages of this PostgreSQL

An open source SQL standard compliant RDBMS, PostgreSQL is the open source as well as free. This is the powerful relational database management system (RDBMS). It has strong community that is supported by a devoted as well as experienced community that can be accessed with the knowledge of the bases and also Q&A sites.

The support of Strong third party, this is the extremely advanced feature. Basically, this is with many of the great as well as open source third party tools. This tolls are used for designing, managing and also for the management system. It is very Extensible, this is to extend the programmatically that is with stored procedures.

This is like the advanced RDBMS. The Objective of this is, it is not just a relational database management system but also an objective one that is with the support for nesting and more as well.

Disadvantages of PostgreSQL

The Performance of this is very simple and read heavy operations. This can be an over kill and also might be appeared in less performing than the other counterparts such as MySQL. The Popularity is given to the nature of this tool.

It also lacks behind in the terms of popularity and also very large amount of deployments. The hosting is also a disadvantage that is very harder to come by the hosts or else service providers. These offers are managed by these instances only.
When to Use
Data integrity
When the reliability and also data integrity are at the absolute necessity without any excuses then PostgreSQL is the better choice to use.
Complex and Custom Procedures
If you require that your database must perform the custom procedures then PostgreSQL is being extensible and it is the better choice to use.

Integration
PostgreSQL will be the most important compliant and is very easy to handle base for the switch.
Complex designs
When compared to the open source as well as free RDBMS implementations for the complex database designs. This PostgreSQL offers the most in terms of functionality and also possibilities without giving up on the other valuable resources.

Mysql Vs Postgresql

The advantages of MySQL
It is very easy to work with Mysql and also easy to install as well. The Third party tools which include the visual ones like GUI (Graphical User Interface) will make it extremely simple to get started with the database. It has the feature advantage that supports a lot of the SQL functionality which is expected from the RDBMS either in the direct way or indirect way.

 It is very secure; a lot of security features are built in this Mysql. It is very scalable and also very powerful. It can handle a lot of data and also the can be used “at scale”, if needed. It gives up some standards in which it allows MySql to work very efficiently and also the cut corners.

Disadvantages of MySQL

By the design itself, MySQL do not any propose to do everything and it comes with some functional limitations as well. Some State of the art applications may be required.
Reliability issues
Certainly the functionality gets handled with MySQL (for example like references, transactions, auditing etc.).
Stagnated Development
This is still very technical on the open source product. There are some complaints regarding the development process since its achievement. However, it should be noted that there are some MySQL-based or else fully integrated databases in which that add a value on the top of the standard MySQL installations.

When To Use MySQL
Distributed Operations
When you need more than what this Postgresql can offer, in that time including this MySql to the deployment of the stack. It is just like stand alone database server. It brings a lot of operational freedom that is together with some advanced features.

It provides High security features that are most dependable protection for data-access and it used in a simple way. It is to its rich configuration settings and also operation modes.

Mysql to Postgresql

First you have to check the server running and then follow the below commands to change Mysql to Postgresql.
su –
su – postgres
createdb test
psql test
=# create user username password ‘ password ‘;
— To change a password:
=# alter role username password ‘ password ‘;
=# create database databasename with encoding ‘utf8’;
=# grant all privileges on database databasename to username;
=# l
=# c databasename
=# q
Be SURE to cover this security issue that is with iptables
/etc/init.d/postgresql reloads or /usr/lib/postgresql/bin/pg_ctl reload
Postmaster successfully signaled
psql -h server -d databasename -U username
databasename=>
Then you must covert and import and this is the common way with SQL dump. Dump your tables with below commands
mysqldump -u username -p –compatible=postgresql databasename > outputfile.sql or else you can dump with the following lines.
SET standard_conforming_strings = ‘off’;
SET backslash_quote = ‘on’;
After converting the tables then you can use them in the same way that were used in Mysql
psql -h server -d databasename -U username -f data.sql

Mysql Postgresql

MySQL PostgreSQL is the decision for most of the users in which it must make the approaching of this open source relational database management systems.

Both are this are time proven solutions that are strongly with some proprietary database software. MySQL will run faster when compared to the other. And most of the people use this only. PostgreSQL was assumed to be more tightly featured database system that are often described as an open source that is version of Oracle.

MySQL is very popular among all the various software projects. This is because of its speed and also it is very easy to use. MySQL has come in a very long way in adding all the advanced functionality while the other is with dramatically improved its speed and also within the last few major releases.

Many of the people are very ignorant of the convergence and they still hold on to stereotypes that are based on MySQL 4.1 and this PostgreSQL 7.4. The current versions are 5.7 for MySQL and 9.4 for PostgreSQL.

Migrate Mysql to Postgresql

Always these migrations are very horrible. This migration does not matter from which system to which system. There are many different reasons why the user wants to migrate. Mostly there are many migrations from MySQL to PostgreSQL. Below are following reasons for this migration.
>> Due to some Performance reasons
>> Stableness
>> Reliability
>> GIS
>> Replication issues
>> Cluster issues
>> High Availability issues
>> Localization or else globalization issues
>> In need of object relational features
>> Sick of bugs or errors
>> Costs for license (dual license or else GPL)
>> License (BSD instead of GPL)
PostgreSQL is the unified database server in which it is with single storage engine. This MySQL has two layers that are an upper SQL layer and also a set of storage engines. While MySQL 5.6 supports 9 storage engines in which the most popular choices are InnoDB, MyISAM and also NDB Cluster.

Storage engines are different in the both functionality and also the performance characteristics. So, it is very important to clarify which is being used in any of the comparisons. When coming to performance, this Database system can be optimized according to the environment that they run in. Thus, it is very difficult to give an accurate or correct comparison in the performance that is without paying the attention to configuration and even environment. These both of them employ the various technologies to improve all the performance.

MySQL has begun in the development with a focus on speed and coming to PostgreSQL it has began with the development on the focus of features as well as standards.

Thus, in which Mysql has recorded very faster of the two. This both DBMS’s will do better in the benchmarks that are related to their original strengths that is MySQL is very fast in simple operations and PostgreSQL is more reliable as well as faster in the complex operations. Thus, this is completely about the Mysql and Postgresql.

(Source: https://www.wikivs.com/wiki/MySQL_vs_PostgreSQL )

LVM ADD NEW HARDDISK

#15:36 2016.04.01
#################
#################STATUS:
#####################################
root@srv152bk:~# df -h
Filesystem                     Size  Used Avail Use% Mounted on
udev                           2.0G  4.0K  2.0G   1% /dev
tmpfs                          396M  748K  395M   1% /run
/dev/sdb1                       46G   26G   18G  60% /
none                           4.0K     0  4.0K   0% /sys/fs/cgroup
none                           5.0M     0  5.0M   0% /run/lock
none                           2.0G     0  2.0G   0% /run/shm
none                           100M     0  100M   0% /run/user
/dev/mapper/vg_srv152-lv01     7.2T  6.7T  127G  99% /disk1
#####################################root@srv152bk:~# lsblk -o NAME,FSTYPE,SIZE,MOUNTPOINT,LABEL
NAME                      FSTYPE        SIZE MOUNTPOINT LABEL
sda                                     5.5T           
sdb                                     1.8T           
+-sdb1                    ext4         46.6G /         
+-sdb2                                    1K           
+-sdb5                    swap          7.5G [SWAP]    
+-sdb6                    LVM2_member   1.8T           
  +-vg_srv152-lv01 (dm-0) ext4          7.2T /disk1    
sdc                                     5.5T           
+-sdc1                    LVM2_member   5.5T           
  +-vg_srv152-lv01 (dm-0) ext4          7.2T /disk1    
sdd                                     1.8T           
+-sdd1                    ntfs          1.8T            2tb_2016.03.29#####################################

root@srv152bk:~# fdisk -l | grep dev

WARNING: GPT (GUID Partition Table) detected on '/dev/sdc'! The util fdisk doesn't support GPT. Use GNU Parted.

Disk /dev/sda doesn't' contain a valid partition table

#Thông tin LVM:

Disk /dev/mapper/vg_srv152-lv01 doesn't contain a valid partition table

#Ổ cứng rời, 2TB, cài OS: WD RED:

Disk /dev/sdb: 2000.4 GB, 2000398934016 bytes

/dev/sdb1   *        2048    97656831    48827392   83  Linux

/dev/sdb2        97658878  3907028991  1904685057    5  Extended

/dev/sdb5        97658880   113281023     7811072   82  Linux swap / Solaris

/dev/sdb6       113283072  3907028991  1896872960   83  Linux

#Ổ cứng rời, 6TB, 64MB CACHE: SEAGATE:

Disk /dev/sdc: 6001.2 GB, 6001175126016 bytes

/dev/sdc1               1  4294967295  2147483647+  ee  GPT

#Ổ cứng rời, 6TB/128MB CACHE: WD RE:

Disk /dev/sda: 6001.2 GB, 6001175126016 bytes

Disk /dev/mapper/vg_srv152-lv01: 7938.5 GB, 7938475294720 bytes

#Ổ cứng di động:

Disk /dev/sdd: 2000.4 GB, 2000365289472 bytes

/dev/sdd1            2048  3906963455  1953480704    7  HPFS/NTFS/exFAT

root@srv152bk:~#

root@srv152bk:~# pvs
  PV         VG        Fmt  Attr PSize PFree
  /dev/sdb6  vg_srv152 lvm2 a--  1.77t 4.74g
  /dev/sdc1  vg_srv152 lvm2 a--  5.46t    0


root@srv152bk:~# pvscan
  PV /dev/sdc1   VG vg_srv152   lvm2 [5.46 TiB / 0    free]
  PV /dev/sdb6   VG vg_srv152   lvm2 [1.77 TiB / 4.74 GiB free]
  Total: 2 [7.22 TiB] / in use: 2 [7.22 TiB] / in no VG: 0 [0   ]


root@srv152bk:~# pvdisplay
  --- Physical volume ---
  PV Name               /dev/sdc1
  VG Name               vg_srv152
  PV Size               5.46 TiB / not usable 0  
  Allocatable           yes (but full)
  PE Size               4.00 MiB
  Total PE              1430791
  Free PE               0
  Allocated PE          1430791
  PV UUID               ORpKCw-wJca-7yK1-hzEU-Tb4B-HxMO-cZuyMh
  
  --- Physical volume ---
  PV Name               /dev/sdb6
  VG Name               vg_srv152
  PV Size               1.77 TiB / not usable 3.00 MiB
  Allocatable           yes
  PE Size               4.00 MiB
  Total PE              463103
  Free PE               1214
  Allocated PE          461889
  PV UUID               wSCZ7y-S3lW-n68t-kotl-o8Ti-ihL4-sR9vFh


root@srv152bk:~# vgs
  VG        #PV #LV #SN Attr   VSize VFree
  vg_srv152   2   1   0 wz--n- 7.22t 4.74g


root@srv152bk:~# vgscan
  Reading all physical volumes.  This may take a while...
  Found volume group "vg_srv152" using metadata type lvm2


root@srv152bk:~# vgdisplay
  --- Volume group ---
  VG Name               vg_srv152
  System ID            
  Format                lvm2
  Metadata Areas        2
  Metadata Sequence No  4
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                1
  Open LV               1
  Max PV                0
  Cur PV                2
  Act PV                2
  VG Size               7.22 TiB
  PE Size               4.00 MiB
  Total PE              1893894
  Alloc PE / Size       1892680 / 7.22 TiB
  Free  PE / Size       1214 / 4.74 GiB
  VG UUID               8yZ5zn-zOPo-YIeB-9hsF-9l4n-ApiR-qgA2uP


root@srv152bk:~# lvs
  LV   VG        Attr      LSize Pool Origin Data%  Move Log Copy%  Convert
  lv01 vg_srv152 -wi-ao--- 7.22t    


root@srv152bk:~# lvscan
  ACTIVE            '/dev/vg_srv152/lv01' [7.22 TiB] inherit


root@srv152bk:~# lvdisplay
  --- Logical volume ---
  LV Path                /dev/vg_srv152/lv01
  LV Name                lv01
  VG Name                vg_srv152
  LV UUID                HWnoX4-SVm6-KoQp-zP00-P1cJ-yaK6-HQ2T6P
  LV Write Access        read/write
  LV Creation host, time srv152bk, 2015-08-22 16:32:46 +0700
  LV Status              available
  # open                 1
  LV Size                7.22 TiB
  Current LE             1892680
  Segments               2
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           252:0
  
#CHUAN BI CAU HINH LVM:

#CHUAN BI CAU HINH LVM:

fdisk /dev/sda #KHONG AP DUNG DUOC VI FDISK CHI NHAN MAX 2TB:

#p       print the partition table

#n       add a new partition

#p       primary partition (1-4)

#1       Partition number (1-4):

#enter   First cylinder (1-130, default 1):

#enter   Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):

#w (write)
 

#CHUYEN SANG GPT:

#__________________________________To create a partition start GNU parted as follows:

#root@srv152bk:~# parted /dev/sda

GNU Parted 2.3

Using /dev/sda

Welcome to GNU Parted! Type 'help' to view a list of commands.

#Creates a new GPT disklabel i.e. partition table:

#(parted) mklabel gpt

Warning: The existing disk label on /dev/sda will be destroyed and all data on this disk will be lost. Do you want to continue?

Yes/No?Yes

#Next, set the default unit to TB, enter:

#(parted) unit TB

#To create a 3TB partition size, enter:

#(parted) mkpart primary 0 0

OR

#(parted) mkpart primary 0.00TB 6.00TB #(CHOOSE THIS COMMAND), Ổ CỨNG 6TB

#To print the current partitions, enter:

#(parted) print                                                           

Model: ATA WDC WD6001FSYZ-0 (scsi)

Disk /dev/sda: 6.00TB

Sector size (logical/physical): 512B/4096B

Partition Table: gpt

Number  Start   End     Size    File system  Name     Flags

 1      0.00TB  6.00TB  6.00TB               primary

#Quit and save the changes, enter:

#(parted) quit

Information: You may need to update /etc/fstab

#root@srv152bk:~# fdisk -l | grep dev

WARNING: GPT (GUID Partition Table) detected on '/dev/sda'! The util fdisk doesn't support GPT. Use GNU Parted.

Disk /dev/mapper/vg_srv152-lv01 doesn't contain a valid partition table

Disk /dev/sda: 6001.2 GB, 6001175126016 bytes

/dev/sda1               1  4294967295  2147483647+  ee  GPT #<<GPT

#Use the mkfs.ext4 command to format the file system, enter:

#root@srv152bk:~# mkfs.ext4 /dev/sda1

mke2fs 1.42.9 (4-Feb-2014)

Filesystem label=

OS type: Linux

Block size=4096 (log=2)

Fragment size=4096 (log=2)

Stride=0 blocks, Stripe width=0 blocks

183144448 inodes, 1465130240 blocks

73256512 blocks (5.00%) reserved for the super user

First data block=0

Maximum filesystem blocks=4294967296

44713 block groups

32768 blocks per group, 32768 fragments per group

4096 inodes per group

Superblock backups stored on blocks:

        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,

        4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,

        102400000, 214990848, 512000000, 550731776, 644972544

Allocating group tables: done                           

Writing inode tables: done                           

Creating journal (32768 blocks): done

Writing superblocks and filesystem accounting information: done

#

########

####################

root@srv152bk:/opt/script# pvcreate /dev/sda1

  Physical volume "/dev/sda1" successfully created

root@srv152bk:/opt/script# pvs

  PV         VG        Fmt  Attr PSize PFree

  /dev/sda1            lvm2 a--  5.46t 5.46t

  /dev/sdb6  vg_srv152 lvm2 a--  1.77t 4.74g

  /dev/sdc1  vg_srv152 lvm2 a--  5.46t    0

root@srv152bk:/opt/script# vgs

  VG        #PV #LV #SN Attr   VSize VFree

  vg_srv152   2   1   0 wz--n- 7.22t 4.74g

root@srv152bk:/opt/script# vgextend  vg_srv152 /dev/sda1

  Volume group "vg_srv152" successfully extended

  VG Size               12.68 TiB

root@srv152bk:~# vgdisplay

  --- Volume group ---

  VG Name               vg_srv152

  System ID            

  Format                lvm2

  Metadata Areas        3

  Metadata Sequence No  8

  VG Access             read/write

  VG Status             resizable

  MAX LV                0

  Cur LV                1

  Open LV               1

  Max PV                0

  Cur PV                3

  Act PV                3

  VG Size               12.68 TiB

  PE Size               4.00 MiB

  Total PE              3324685

  Alloc PE / Size       1892680 / 7.22 TiB (Dung lượng hiện tại)

  Free  PE / Size       1432005 / 5.46 TiB (FREE, chưa add vào LVM)

  VG UUID               8yZ5zn-zOPo-YIeB-9hsF-9l4n-ApiR-qgA2uP

  

  

root@srv152bk:~# lvscan

  ACTIVE            '/dev/vg_srv152/lv01' [7.22 TiB] inherit

root@srv152bk:~# lvresize -L+5.46TB /dev/vg_srv152/lv01

  Rounding size to boundary between physical extents: 5.46 TiB

  Extending logical volume lv01 to 12.68 TiB

  Logical volume lv01 successfully resized

root@srv152bk:~# resize2fs /dev/vg_srv152/lv01

resize2fs 1.42.9 (4-Feb-2014)

Filesystem at /dev/vg_srv152/lv01 is mounted on /disk1; on-line resizing required

old_desc_blocks = 463, new_desc_blocks = 812

The filesystem on /dev/vg_srv152/lv01 is now 3403762688 blocks long.

#KẾT QUẢ: