Revision 13289
Added by Aaron Marcuse-Kubitza over 10 years ago
| index.htm | ||
|---|---|---|
| 714 | 714 |
<DD>"One thing you can use these for is finding duplicate data in a table. Let’s say you’re trying to add a unique constraint, but one row in the table is duplicated 3 times, which blocks the unique index from being created. When rows are identical in every column, you can’t write any simple SELECT statement to uniquely identify them. That means deleting all of them but one copy requires some annoying and fragile SQL code, combining DELETE with LIMIT and/or OFFSET–which is always scary. If you use the ctid instead, the implementation will be PostgreSQL specific, but it will also be faster and cleaner. See Deleting Duplicate Records in a Table for an example of how that can be done." |
| 715 | 715 |
</DL><p> |
| 716 | 716 |
<DT><a name="backups" href="#backups"><H3 ADD_DATE="1397769882">backups</H3></a> |
| 717 |
<DD>online backups: |
|
| 718 |
|
|
| 719 |
pg_start_backup() method: |
|
| 720 |
(more reliable, but requires informing the DB server to prepare for a backup) |
|
| 721 |
# configure: |
|
| 722 |
## in postgresql.conf, set wal_level = hot_standby |
|
| 723 |
## restart server |
|
| 724 |
# run: |
|
| 725 |
## $ sudo -u postgres psql <<<"SELECT pg_start_backup('backup', true);" |
|
| 726 |
## copy files using rsync, etc. |
|
| 727 |
## $ sudo -u postgres psql <<<"SELECT pg_stop_backup();" |
|
| 728 |
|
|
| 729 |
wal_keep_segments method: |
|
| 730 |
(potentially less reliable, but completely independent of the DB server) |
|
| 731 |
</DD> |
|
| 717 | 732 |
<DL><p> |
| 718 | 733 |
<DT><a name="pg_clog, pg_xlog" href="#pg_clog, pg_xlog"><H3 ADD_DATE="1397777677">pg_clog, pg_xlog</H3></a> |
| 719 | 734 |
<DL><p> |
| ... | ... | |
| 742 | 757 |
not be capable of replaying whatever happened since your last checkpoint." |
| 743 | 758 |
<DT><A HREF="http://www.postgresql.org/message-id/CB0FB369FF86E248A884BCC002562BCB010C0D9F@pisgsna01sxch01.ana.firstamdata.com" name="http://www.postgresql.org/message-id/CB0FB369FF86E248A884BCC002562BCB010C0D9F@pisgsna01sxch01.ana.firstamdata.com" ADD_DATE="1397777667" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>Re: what are the pg_clog and pg_xlog directories ?</A><a name="Re: what are the pg_clog and pg_xlog directories ?" href="#Re: what are the pg_clog and pg_xlog directories ?" style="margin-left: 0.5em;">¶</a> |
| 744 | 759 |
</DL><p> |
| 760 |
<DT><A HREF="http://www.postgresql.org/docs/9.3/static/runtime-config-wal.html" name="http://www.postgresql.org/docs/9.3/static/runtime-config-wal.html" ADD_DATE="1397779174" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>** PostgreSQL: Documentation: 9.3: Write Ahead Log</A><a name="** PostgreSQL: Documentation: 9.3: Write Ahead Log" href="#** PostgreSQL: Documentation: 9.3: Write Ahead Log" style="margin-left: 0.5em;">¶</a> |
|
| 761 |
<DD>"But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so either archive or hot_standby level must be used to enable WAL archiving (archive_mode) and streaming replication." |
|
| 762 |
|
|
| 763 |
"In many situations, turning off synchronous_commit for noncritical transactions can provide much of the potential performance benefit of turning off fsync, without the attendant risks of data corruption." |
|
| 764 |
|
|
| 765 |
"it is only advisable to turn off fsync if you can easily recreate your entire database from external data" (which is the case for us, actually, but we currently turn off synchronous_commit instead) |
|
| 766 |
** note that turning fsync on protects against power failure, but not hard disk failure or human error. for that, you need continuous archiving or point-in-time recovery, respectively. |
|
| 767 |
|
|
| 768 |
"In hot_standby level, the same information is logged as with archive, plus information needed to reconstruct the status of running transactions from the WAL. [...] It is thought that there is little measurable difference in performance between using hot_standby and archive levels" ie, if you are going to use archive anyway, might as well go straight to hot_standby |
|
| 745 | 769 |
<DT><A HREF="http://www.postgresql.org/docs/9.3/static/continuous-archiving.html" name="http://www.postgresql.org/docs/9.3/static/continuous-archiving.html" ADD_DATE="1397776277" ICON_URI="http://www.postgresql.org/favicon.ico"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>*** PostgreSQL: Documentation: 9.3: Continuous Archiving and Point-in-Time Recovery (PITR)</A><a name="*** PostgreSQL: Documentation: 9.3: Continuous Archiving and Point-in-Time Recovery (PITR)" href="#*** PostgreSQL: Documentation: 9.3: Continuous Archiving and Point-in-Time Recovery (PITR)" style="margin-left: 0.5em;">¶</a> |
| 746 | 770 |
<DD>"To prepare for low level standalone hot backups, set wal_level to archive (or hot_standby), archive_mode to on, and set up an archive_command that performs archiving only when a switch file exists." |
| 747 | 771 |
|
| 748 | 772 |
to work with a filesystem-level backup, you need a WAL chain that's at least long enough (time-wise) for the WAL blocks created when it starts copying base/ to still be around when it finishes copying pg_xlog/ (it must also copy in alphabetical order, and not in parallel). |
| 773 |
<DT><A HREF="http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS" name="http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS" ADD_DATE="1398196461" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>*** PostgreSQL: Documentation: 9.3: Replication: wal_keep_segments</A><a name="*** PostgreSQL: Documentation: 9.3: Replication: wal_keep_segments" href="#*** PostgreSQL: Documentation: 9.3: Replication: wal_keep_segments" style="margin-left: 0.5em;">¶</a> |
|
| 774 |
<DD>page's self-description: online backups: |
|
| 775 |
|
|
| 776 |
pg_start_backup() method: |
|
| 777 |
(more reliable, but requires putting the DB server into a special mode during backup) |
|
| 778 |
# configure: |
|
| 779 |
## in postgresql.conf, set wal_level = hot_standby |
|
| 780 |
## $ sudo service postgresql restart |
|
| 781 |
# run: |
|
| 782 |
## $ sudo -u postgres psql <<<"SELECT pg_start_backup('backup', true);" |
|
| 783 |
## copy files using rsync, etc. |
|
| 784 |
## $ sudo -u postgres psql <<<"SELECT pg_stop_backup();" |
|
| 785 |
|
|
| 786 |
wal_keep_segments method: |
|
| 787 |
(less reliable, but the backup is completely independent of the DB server) |
|
| 788 |
# configure: |
|
| 789 |
## in postgresql.conf: |
|
| 790 |
### set wal_level = hot_standby |
|
| 791 |
### set wal_keep_segments to long enough that the WAL chain spans the duration of the backup |
|
| 792 |
#### to determine this, measure how quickly WAL segments are created at peak DB usage |
|
| 793 |
## $ sudo service postgresql restart |
|
| 794 |
# run: |
|
| 795 |
## copy files using rsync, etc. |
|
| 796 |
## copy again to get changes since last backup |
|
| 797 |
## copy again to make sure there are no more changes |
|
| 798 |
|
|
| 799 |
offline backups: |
|
| 800 |
|
|
| 801 |
# copy files using rsync, etc. |
|
| 802 |
# copy again to get changes since last backup |
|
| 803 |
# $ sudo service postgresql stop |
|
| 804 |
# copy again to make the copy a consistent snapshot |
|
| 805 |
# $ sudo service postgresql start |
|
| 749 | 806 |
<DT><A HREF="http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-parallel-pg_dump/" name="http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-parallel-pg_dump/" ADD_DATE="1397769449" ICON_URI="http://michael.otacoo.com/content/favicon_otacoo.gif" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://michael.otacoo.com/content/favicon_otacoo.gif" style="margin-right: 0.5em;"/>Postgres 9.3 feature highlight: parallel pg_dump</A><a name="Postgres 9.3 feature highlight: parallel pg_dump" href="#Postgres 9.3 feature highlight: parallel pg_dump" style="margin-left: 0.5em;">¶</a> |
| 750 | 807 |
<DD>page's self-description: Postgres 9.3 feature highlight: parallel pg_dump |
| 808 |
<DT><A HREF="http://www.postgresql.org/docs/9.3/static/sql-checkpoint.html" name="http://www.postgresql.org/docs/9.3/static/sql-checkpoint.html" ADD_DATE="1398205258" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>PostgreSQL: Documentation: 9.3: CHECKPOINT</A><a name="PostgreSQL: Documentation: 9.3: CHECKPOINT" href="#PostgreSQL: Documentation: 9.3: CHECKPOINT" style="margin-left: 0.5em;">¶</a> |
|
| 809 |
<DD>"A checkpoint is a point in the transaction log sequence at which all data files have been updated to reflect the information in the log" |
|
| 751 | 810 |
<DT><A HREF="http://www.postgresql.org/docs/9.3/static/backup-file.html" name="http://www.postgresql.org/docs/9.3/static/backup-file.html" ADD_DATE="1397776134" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>PostgreSQL: Documentation: 9.3: File System Level Backup</A><a name="PostgreSQL: Documentation: 9.3: File System Level Backup" href="#PostgreSQL: Documentation: 9.3: File System Level Backup" style="margin-left: 0.5em;">¶</a> |
| 752 | 811 |
<DD>"The database server must be shut down in order to get a usable backup. Half-way measures such as disallowing all connections will not work (in part [...] because of internal buffering within the server)." |
| 753 | 812 |
<DT><A HREF="http://www.postgresql.org/docs/9.3/static/app-pgbasebackup.html" name="http://www.postgresql.org/docs/9.3/static/app-pgbasebackup.html" ADD_DATE="1397778881" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>PostgreSQL: Documentation: 9.3: pg_basebackup</A><a name="PostgreSQL: Documentation: 9.3: pg_basebackup" href="#PostgreSQL: Documentation: 9.3: pg_basebackup" style="margin-left: 0.5em;">¶</a> |
| 754 |
<DT><A HREF="http://www.postgresql.org/docs/9.3/static/runtime-config-wal.html" name="http://www.postgresql.org/docs/9.3/static/runtime-config-wal.html" ADD_DATE="1397779174" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>** PostgreSQL: Documentation: 9.3: Write Ahead Log</A><a name="** PostgreSQL: Documentation: 9.3: Write Ahead Log" href="#** PostgreSQL: Documentation: 9.3: Write Ahead Log" style="margin-left: 0.5em;">¶</a> |
|
| 755 |
<DD>"But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so either archive or hot_standby level must be used to enable WAL archiving (archive_mode) and streaming replication." |
|
| 756 |
|
|
| 757 |
"In many situations, turning off synchronous_commit for noncritical transactions can provide much of the potential performance benefit of turning off fsync, without the attendant risks of data corruption." |
|
| 758 |
|
|
| 759 |
"it is only advisable to turn off fsync if you can easily recreate your entire database from external data" (which is the case for us, actually, but we currently turn off synchronous_commit instead) |
|
| 760 |
** note that turning fsync on protects against power failure, but not hard disk failure or human error. for that, you need continuous archiving or point-in-time recovery, respectively. |
|
| 761 |
|
|
| 762 |
"In hot_standby level, the same information is logged as with archive, plus information needed to reconstruct the status of running transactions from the WAL. [...] It is thought that there is little measurable difference in performance between using hot_standby and archive levels" ie, if you are going to use archive anyway, might as well go straight to hot_standby |
|
| 763 | 813 |
</DL><p> |
| 764 | 814 |
<DT><a name="disk space" href="#disk space"><H3 ADD_DATE="1340661986">disk space</H3></a> |
| 765 | 815 |
<DL><p> |
| ... | ... | |
| 1710 | 1760 |
<DL><p> |
| 1711 | 1761 |
<DT><A HREF="http://ubuntuforums.org/showthread.php?t=1222909" name="http://ubuntuforums.org/showthread.php?t=1222909" ADD_DATE="1329253743" ICON_URI="http://ubuntuforums.org/favicon.ico" LAST_CHARSET="ISO-8859-1"><img width="16" height="16" src="http://ubuntuforums.org/favicon.ico" style="margin-right: 0.5em;"/>[ubuntu] On login 5 packages can be updated - Ubuntu Forums</A><a name="[ubuntu] On login 5 packages can be updated - Ubuntu Forums" href="#[ubuntu] On login 5 packages can be updated - Ubuntu Forums" style="margin-left: 0.5em;">¶</a> |
| 1712 | 1762 |
<DD>page's self-description: [ubuntu] On login 5 packages can be updated Server Platforms |
| 1713 |
<DT><A HREF="http://www.howtogeek.com/75705/access-shared-folders-in-a-virtualbox-ubuntu-11.04-virtual-machine/" name="http://www.howtogeek.com/75705/access-shared-folders-in-a-virtualbox-ubuntu-11.04-virtual-machine/" ADD_DATE="1365498064" ICON_URI="http://cdn.howtogeek.com/public/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://cdn.howtogeek.com/public/favicon.ico" style="margin-right: 0.5em;"/>Access Shared Folders in a VirtualBox Ubuntu 11.04 Virtual Machine</A><a name="Access Shared Folders in a VirtualBox Ubuntu 11.04 Virtual Machine" href="#Access Shared Folders in a VirtualBox Ubuntu 11.04 Virtual Machine" style="margin-left: 0.5em;">¶</a> |
|
| 1714 |
<DD>page's self-description: Have you installed Ubuntu 11.04 as a virtual machine in VirtualBox but have had problems getting the Shared Folders feature to work? We were able to add a shared folder, but were unable to access it. |
|
| 1715 |
<DT><A HREF="https://help.ubuntu.com/community/KVM/VirtManager" name="https://help.ubuntu.com/community/KVM/VirtManager" ADD_DATE="1367519739" ICON_URI="https://help.ubuntu.com/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="https://help.ubuntu.com/favicon.ico" style="margin-right: 0.5em;"/>KVM/VirtManager - Community Ubuntu Documentation</A><a name="KVM/VirtManager - Community Ubuntu Documentation" href="#KVM/VirtManager - Community Ubuntu Documentation" style="margin-left: 0.5em;">¶</a> |
|
| 1716 | 1763 |
<DT><A HREF="http://ubuntuforums.org/showthread.php?t=2058377" name="http://ubuntuforums.org/showthread.php?t=2058377" ADD_DATE="1373365205" ICON_URI="http://ubuntuforums.org/favicon.ico" LAST_CHARSET="windows-1252"><img width="16" height="16" src="http://ubuntuforums.org/favicon.ico" style="margin-right: 0.5em;"/>[ubuntu] remove old linux kernels</A><a name="[ubuntu] remove old linux kernels" href="#[ubuntu] remove old linux kernels" style="margin-left: 0.5em;">¶</a> |
| 1717 | 1764 |
<DD>page's self-description: How does one remove the extra entries of the old linux kernels after upgrade of a new version. Remember Synaptic used to do that but it was the case when Startup manager used to do a good job. Please help if i'm missing something. |
| 1718 | 1765 |
|
| ... | ... | |
| 1724 | 1771 |
sudo apt-get --purge remove <Kernel> |
| 1725 | 1772 |
sudo update-grub |
| 1726 | 1773 |
" |
| 1774 |
<DT><A HREF="http://itsfoss.com/things-to-do-after-installing-ubuntu-14-04/" name="http://itsfoss.com/things-to-do-after-installing-ubuntu-14-04/" ADD_DATE="1398211354" ICON_URI="http://itsfoss.com/wp-content/themes/focus-pro/images/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://itsfoss.com/wp-content/themes/focus-pro/images/favicon.ico" style="margin-right: 0.5em;"/>** 14 Things To Do After Installing Ubuntu 14.04</A><a name="** 14 Things To Do After Installing Ubuntu 14.04" href="#** 14 Things To Do After Installing Ubuntu 14.04" style="margin-left: 0.5em;">¶</a> |
|
| 1775 |
<DD>page's self-description: Confused what to do after Installing Ubuntu 14.04? Here are some essential things to do after installing Ubuntu 14.04 Trusty Tahr. |
|
| 1776 |
<DT><A HREF="http://www.howtogeek.com/75705/access-shared-folders-in-a-virtualbox-ubuntu-11.04-virtual-machine/" name="http://www.howtogeek.com/75705/access-shared-folders-in-a-virtualbox-ubuntu-11.04-virtual-machine/" ADD_DATE="1365498064" ICON_URI="http://cdn.howtogeek.com/public/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://cdn.howtogeek.com/public/favicon.ico" style="margin-right: 0.5em;"/>Access Shared Folders in a VirtualBox Ubuntu 11.04 Virtual Machine</A><a name="Access Shared Folders in a VirtualBox Ubuntu 11.04 Virtual Machine" href="#Access Shared Folders in a VirtualBox Ubuntu 11.04 Virtual Machine" style="margin-left: 0.5em;">¶</a> |
|
| 1777 |
<DD>page's self-description: Have you installed Ubuntu 11.04 as a virtual machine in VirtualBox but have had problems getting the Shared Folders feature to work? We were able to add a shared folder, but were unable to access it. |
|
| 1778 |
<DT><A HREF="https://help.ubuntu.com/community/KVM/VirtManager" name="https://help.ubuntu.com/community/KVM/VirtManager" ADD_DATE="1367519739" ICON_URI="https://help.ubuntu.com/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="https://help.ubuntu.com/favicon.ico" style="margin-right: 0.5em;"/>KVM/VirtManager - Community Ubuntu Documentation</A><a name="KVM/VirtManager - Community Ubuntu Documentation" href="#KVM/VirtManager - Community Ubuntu Documentation" style="margin-left: 0.5em;">¶</a> |
|
| 1727 | 1779 |
</DL><p> |
| 1728 | 1780 |
<DT><A HREF="http://sourcecodebrowser.com/liblockfile/1.08/lockfile_8h.html#a07dd899d02573b6d2634a27ac2f23204" name="http://sourcecodebrowser.com/liblockfile/1.08/lockfile_8h.html#a07dd899d02573b6d2634a27ac2f23204" ADD_DATE="1359846610" LAST_CHARSET="UTF-8"><img width="16" height="16" src="blank.gif" style="margin-right: 0.5em;"/>liblockfile 1.08, lockfile_8h.html</A><a name="liblockfile 1.08, lockfile_8h.html" href="#liblockfile 1.08, lockfile_8h.html" style="margin-left: 0.5em;">¶</a> |
| 1729 | 1781 |
<DT><A HREF="http://www.cyberciti.biz/faq/linux-delete-user-password/" name="http://www.cyberciti.biz/faq/linux-delete-user-password/" ADD_DATE="1365495065" ICON_URI="http://s0.cyberciti.org/static/legacy/i/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://s0.cyberciti.org/static/legacy/i/favicon.ico" style="margin-right: 0.5em;"/>Linux: Delete user password</A><a name="Linux: Delete user password" href="#Linux: Delete user password" style="margin-left: 0.5em;">¶</a> |
Also available in: Unified diff
web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: backups: documented how to set up online and offline backups (with two possible approaches for online backups)