I am new to Postgres and the database field. My question is that how many copies of each table are stored in physical storage device (memory, disk and so on)? I assume we need more than one copies for each table. If we only have one copy for each table, then it will result in one point failure if that storage device is broken. Is my understanding correct?
Thanks, Zack
It sounds like you might be coming from an Oracle background, where the Oracle database generally manages the storage system directly.
That's not how PostgreSQL works. It uses the file systems, RAID support, etc provided by the operating system.
To get redundant storage with PostgreSQL you need to set up redundant stoage - in the form of RAID, or whatever - in the underlying operating system. Then simply put the PostgreSQL data directory and tablespace(s) on file systems that are stored on the redundant storage array(s).
A common configuration for small installs is:
SATA disk 1 SATA disk 2
| |
-----------------------
|
v
[Linux 'md' software RAID driver, RAID 1]
|
v
[ext4 file system mounted on /var/lib/pgsql]
|
v
[PostgreSQL datadir in /var/lib/pgsql/9.3/data]
Of course, that does you no good at all if the machine is destroyed by fire/lightning surge/etc, or someone does an rm -rf /var/lib/pgsql. Which is why you should also have replication to another machine, preferably off-site.
Replication won't help you if someone does a DROP TABLE precious_data; ... which is why you should additionally have frequent logical backups (pg_dump) and/or point in time recovery with WAL archiving.
