-
BASIC POSTGRESQL
6-
PostgreSQL Brief History 30 minLecture1.1
-
PostgreSQL Features 20 minLecture1.2
-
PostgreSQL Monitoring Tools Overview 30 minLecture1.3
-
PostgreSQL – Oracle Vs PostgreSQL 30 minLecture1.4
-
PostgreSQL Data Types 30 minLecture1.5
-
PostgreSQL Maximum Objects Size 30 minLecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Vmware Installation 30 minLecture2.1
-
Creating Linux Virtual Machine 01 hourLecture2.2
-
PostgreSQL Installation Types 30 minLecture2.3
-
PostgreSQL GUI Installation 30 minLecture2.4
-
PostgreSQL Text Mode Installation 30 minLecture2.5
-
PostgreSQL Unattended Mode Installation 30 minLecture2.6
-
Configure the network & Disk Partition 30 minLecture2.7
-
How to install PostgreSQL10 On Linux Server ? 20 minLecture2.8
-
PostgreSQL -11 Installation (rpm & source code) 30 minLecture2.9
-
-
POSTGRESQL DATABASE
6-
Connect Postgres Server 10 minLecture3.1
-
PostgreSQL startup / shutdown /restart the postgresql server 30 minLecture3.2
-
PostgreSQL .bash_profile Set Up 30 minLecture3.3
-
PostgreSQL Database Creation 30 minLecture3.4
-
PostgreSQL Connect Database 30 minLecture3.5
-
PostgreSQL Drop Database 30 minLecture3.6
-
-
POSTGRESQL TABLE
16-
PostgreSQL Languages 30 minLecture4.1
-
PostgreSQL Create Table 30 minLecture4.2
-
PostgreSQL Select Table 30 minLecture4.3
-
PostgreSQL Alter Table 30 minLecture4.4
-
PostgreSQL Drop Table 30 minLecture4.5
-
PostgreSQL Truncate Table 30 minLecture4.6
-
PostgreSQL Rename 30 minLecture4.7
-
PostgreSQL Comment 30 minLecture4.8
-
PostgreSQL Insert 30 minLecture4.9
-
PostgreSQL Update 30 minLecture4.10
-
PostgreSQL Table Delete 30 minLecture4.11
-
PostgreSQL Merge Table 30 minLecture4.12
-
PostgreSQL UNION 30 minLecture4.13
-
PostgreSQL UNION ALL 30 minLecture4.14
-
PostgreSQL INTERSECT 30 minLecture4.15
-
PostgreSQL DISTINCT 30 minLecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
PostgreSQL Grant 30 minLecture5.1
-
PostgreSQL Revoke 30 minLecture5.2
-
Postgres user creation and restrict DDL statement & other database access 30 minLecture5.3
-
-
TRANSACTIONS - MVCC
3-
PostgreSQL Commit 30 minLecture6.1
-
PostgreSQL Rollback 30 minLecture6.2
-
PostgreSQL Savepoint 30 minLecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
PostgreSQL User Creation 30 minLecture7.1
-
PostgreSQL User Creation Using Utility 30 minLecture7.2
-
PostgreSQL Drop user 30 minLecture7.3
-
PostgreSQL Drop User Using Utility 30 minLecture7.4
-
PostgreSQL Password Changing and Views 30 minLecture7.5
-
PostgreSQL Group/role 30 minLecture7.6
-
Alter PostgreSQL User/Role/group 30 minLecture7.7
-
PostgreSQL Schema 30 minLecture7.8
-
PostgreSQL user creation and restrict DDL & database access 30 minLecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
PostgreSQL Constraints 30 minLecture8.1
-
PostgreSQL Primary Key 30 minLecture8.2
-
PostgreSQL UNIQUE Constraint 30 minLecture8.3
-
PostgreSQL CHECK Constraint 30 minLecture8.4
-
PostgreSQL NOT NULL Constraint 30 minLecture8.5
-
PostgreSQL Foreign Key 30 minLecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
PostgreSQL DOMAIN Data Type 30 minLecture9.1
-
PostgreSQL Alter Domain 30 minLecture9.2
-
PostgreSQL Drop DOMAIN 30 minLecture9.3
-
PostgreSQL Json Data Type 30 minLecture9.4
-
PostgreSQL Sequence 30 minLecture9.5
-
-
POSTGRESQL VIEWS
1-
How to Create PostgreSQL View 30 minLecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
PostgreSQL Tablespace 30 minLecture12.1
-
PostgreSQL UnderStanding Of Objects 30 minLecture12.2
-
PostgreSQL Architecture 30 minLecture12.3
-
PostgreSQL Query Flow 30 minLecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
PostgreSQL Backup & Recovery Overview 30 minLecture13.1
-
PostgreSQL Database Backup Tools – Pg_dump 30 minLecture13.2
-
PostgreSQL Database Backup Tools – Pg_dumpall 30 minLecture13.3
-
PostgreSQL Database Backup Tools – Pg_basebackup 30 minLecture13.4
-
PostgreSQL COPY data From Text File 30 minLecture13.5
-
PostgreSQL COPY data to Text File 30 minLecture13.6
-
PostgreSQL Clone Table 20 minLecture13.7
-
Postgres Database Schema Migration 30 minLecture13.8
-
PostgreSQL Database clone/migration 30 minLecture13.9
-
PostgreSQL Compress backup format 30 minLecture13.10
-
PostgreSQL Archivelog 30 minLecture13.11
-
PostgreSQL Point In Time Recovery 30 minLecture13.12
-
Taking Backup On Postgres Slave (Standby) Server 30 minLecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
PostgreSQL Index 30 minLecture14.1
-
PostgreSQL Reindex 30 minLecture14.2
-
PostgreSQL PerformanceTuning 30 minLecture14.3
-
Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 minLecture14.4
-
Postgres Autovacuum Configuration/Setup 30 minLecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
POSTGRESQL STREAMING REPLICATION 30 minLecture15.1
-
SSL Streaming replication with PostgreSQL 10 30 minLecture15.2
-
How to make Linux partition and configure the network on Redhat Linux7.3 30 minLecture15.3
-
How To Fix Firewall Issues while connecting the other server? 30 minLecture15.4
-
How to install the PostgreSQL10 On Linux ? 30 minLecture15.5
-
How to Configure the cascade replication On PostgreSQL 10.3 ? 30 minLecture15.6
-
How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 minLecture15.7
-
PostgreSQL Switchover 30 minLecture15.8
-
Postgres Failover 30 minLecture15.9
-
Postgres Upgrade 30 minLecture15.10
-
PostgreSQL Upgrade 9.5 to 11.3 . 30 minLecture15.11
-
PostgreSQL Compress backup format
The -F option on pg_dump allows you to specify the format of the output. The default output is plain-text. That’s what you get when you do this:
pg_dump bacula > bacula.sql
and is functionally equivalent to doing:
pg_dump -Fp bacula > bacula.sql
There are other formats (directory, tar), but today I’m concerned with the custom (c) option. By default, this output is also compressed (see the -Z option). What is of most value is the ability to use pg_restore with the custom output.
What’s the big deal about custom format?
The more interesting option on pg_restore was -j. This allows you to run multiple concurrent jobs for the loading of data and building of indexes. This can greatly reduce the amount of time needed to restore a large database if your server has multiple processors.
pg_restore can be much more useful than just doing:
psql bacula < bacula.sql
With pg_restore you can be very selective about what you restore. See the -L and -l options.
How much does pg_dump compress?
I was curious. I wanted to compare the various pg_dump times for each level of compression.
I did a series of test such as this:
$ time pg_dump bacula > bacula.sql real 4m22.654s user 0m17.080s sys 0m7.766s
In the following table, I’m using the ‘real’ value shown above. In addition, I’m also showing the time it took to restore the dump via ‘pg_store -d testing’.
| command | dump time | file size | restore time |
|---|---|---|---|
| pg_dump | 4m22.654s | 13G | 34m56.354s |
| pg_dump -Fc | 11m12.443s | 4.6G | 34m27.218s |
| pg_dump -Z0 -Fc | 4m18.742s | 14G | 36m8.156s |
| pg_dump -Z1 -Fc | 5m0.498s | 5.4G | 36m26.047s |
| pg_dump -Z2 -Fc | 5m24.376s | 5.2G | 36m11.556s |
| pg_dump -Z3 -Fc | 6m20.513s | 5.1G | 36m12.259s |
| pg_dump -Z4 -Fc | 6m46.074s | 4.9G | 36m31.050s |
| pg_dump -Z5 -Fc | 8m22.397s | 4.7G | 34m53.817s |
| pg_dump -Z6 -Fc | 11m18.154s | 4.6G | 35m54.344s |
| pg_dump -Z7 -Fc | 14m21.447s | 4.5G | 35m14.134s |
| pg_dump -Z8 -Fc | 25m15.000s | 4.5G (45MB smaller) | 35m22.793s |
| pg_dump -Z9 -Fc | 26m40.550s | 4.5G (1.8MB smaller) | 35m11.992s |
Conclusions
Looking at the above, the default compression (i.e. without specifying -Z) is about the same as -Z6, but takes only 4 minutes versus 11 minutes.
I will be using just plain -Fc, and I will not be using the -Z option.
pg_restore times
As you can see from the above times, compression level has very little performance hit on pg_restore.
What about -j?
Let’s try -j with pg_restore:
$ time pg_restore -j 8 -d testing < bacula.dump.11m12.443s pg_restore: [custom archiver] parallel restore from standard input is not supported real 0m0.073s user 0m0.000s sys 0m0.000s
Oh, OK, let’s go with this format:
$ time pg_restore -j 8 -d testing bacula.dump.11m12.443s
The original test for this took about 34.5 minutes. I won’t consider -j 8 to be much of a savings unless it’s under 32 minutes. Anything less just might be noise.
*insert pause here*
Wow! 17 minutes! That’s a huge performance increase! I am impressed. I will be using -j every time now.