首页 > 代码库 > PostgreSQL 10 build-in table partition(Range)
PostgreSQL 10 build-in table partition(Range)
1.下载 rpm知识库包
操作系统版本：CentOS Linux release 7.2.1511 (Core) X64
[root@localhost home]# yum install https://download.postgresql.org/pub/repos/yum/testing/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:pgdg-centos10-10-1 ################################# [100%]
2.安装数据库
[root@localhost home]# yum groupinstall "PostgreSQL Database Server 10 PGDG"
Loaded plugins: fastestmirror
There is no installed groups file.
Maybe run: yum groups mark convert (see man yum)
base | 3.6 kB 00:00:00
extras | 3.4 kB 00:00:00
pgdg10-updates-testing | 4.1 kB 00:00:00
updates | 3.4 kB 00:00:00
(1/6): extras/7/x86_64/primary_db | 151 kB 00:00:01
(2/6): base/7/x86_64/group_gz | 155 kB 00:00:07
(3/6): pgdg10-updates-testing/7/x86_64/group_gz | 245 B 00:00:07
(4/6): base/7/x86_64/primary_db | 5.6 MB 00:00:08
(5/6): pgdg10-updates-testing/7/x86_64/primary_db | 79 kB 00:00:09
(6/6): updates/7/x86_64/primary_db | 4.7 MB 00:00:11
Determining fastest mirrors
* base: mirrors.tuna.tsinghua.edu.cn
* extras: mirrors.163.com
* updates: mirrors.163.com
Resolving Dependencies
--> Running transaction check
---> Package postgresql10.x86_64 0:10.0-20170419_1PGDG.rhel7.7 will be installed
---> Package postgresql10-contrib.x86_64 0:10.0-20170419_1PGDG.rhel7.7 will be installed
--> Processing Dependency: libxslt.so.1(LIBXML2_1.0.22)(64bit) for package: postgresql10-contrib-10.0-20170419_1PGDG.rhel7.7.x86_64
--> Processing Dependency: libxslt.so.1(LIBXML2_1.0.18)(64bit) for package: postgresql10-contrib-10.0-20170419_1PGDG.rhel7.7.x86_64
--> Processing Dependency: libxslt.so.1(LIBXML2_1.0.11)(64bit) for package: postgresql10-contrib-10.0-20170419_1PGDG.rhel7.7.x86_64
--> Processing Dependency: libxslt.so.1()(64bit) for package: postgresql10-contrib-10.0-20170419_1PGDG.rhel7.7.x86_64
--> Processing Dependency: libperl.so()(64bit) for package: postgresql10-contrib-10.0-20170419_1PGDG.rhel7.7.x86_64
---> Package postgresql10-libs.x86_64 0:10.0-20170419_1PGDG.rhel7.7 will be installed
---> Package postgresql10-server.x86_64 0:10.0-20170419_1PGDG.rhel7.7 will be installed
--> Processing Dependency: libicuuc.so.50()(64bit) for package: postgresql10-server-10.0-20170419_1PGDG.rhel7.7.x86_64
--> Processing Dependency: libicui18n.so.50()(64bit) for package: postgresql10-server-10.0-20170419_1PGDG.rhel7.7.x86_64
--> Running transaction check
---> Package libicu.x86_64 0:50.1.2-15.el7 will be installed
---> Package libxslt.x86_64 0:1.1.28-5.el7 will be installed
---> Package perl-libs.x86_64 4:5.16.3-291.el7 will be installed
--> Processing Dependency: perl(:MODULE_COMPAT_5.16.3) for package: 4:perl-libs-5.16.3-291.el7.x86_64
--> Running transaction check
---> Package perl.x86_64 4:5.16.3-291.el7 will be installed
--> Processing Dependency: perl(Socket) >= 1.3 for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Scalar::Util) >= 1.10 for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl-macros for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(threads::shared) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(threads) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(constant) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Time::Local) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Time::HiRes) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Storable) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Socket) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Scalar::Util) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Pod::Simple::XHTML) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Pod::Simple::Search) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Getopt::Long) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Filter::Util::Call) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(File::Temp) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(File::Spec::Unix) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(File::Spec::Functions) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(File::Spec) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(File::Path) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Exporter) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Cwd) for package: 4:perl-5.16.3-291.el7.x86_64
--> Processing Dependency: perl(Carp) for package: 4:perl-5.16.3-291.el7.x86_64
--> Running transaction check
---> Package perl-Carp.noarch 0:1.26-244.el7 will be installed
---> Package perl-Exporter.noarch 0:5.68-3.el7 will be installed
---> Package perl-File-Path.noarch 0:2.09-2.el7 will be installed
---> Package perl-File-Temp.noarch 0:0.23.01-3.el7 will be installed
---> Package perl-Filter.x86_64 0:1.49-3.el7 will be installed
---> Package perl-Getopt-Long.noarch 0:2.40-2.el7 will be installed
--> Processing Dependency: perl(Pod::Usage) >= 1.14 for package: perl-Getopt-Long-2.40-2.el7.noarch
--> Processing Dependency: perl(Text::ParseWords) for package: perl-Getopt-Long-2.40-2.el7.noarch
---> Package perl-PathTools.x86_64 0:3.40-5.el7 will be installed
---> Package perl-Pod-Simple.noarch 1:3.28-4.el7 will be installed
--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.28-4.el7.noarch
--> Processing Dependency: perl(Encode) for package: 1:perl-Pod-Simple-3.28-4.el7.noarch
---> Package perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 will be installed
---> Package perl-Socket.x86_64 0:2.010-4.el7 will be installed
---> Package perl-Storable.x86_64 0:2.45-3.el7 will be installed
---> Package perl-Time-HiRes.x86_64 4:1.9725-3.el7 will be installed
---> Package perl-Time-Local.noarch 0:1.2300-2.el7 will be installed
---> Package perl-constant.noarch 0:1.27-2.el7 will be installed
---> Package perl-macros.x86_64 4:5.16.3-291.el7 will be installed
---> Package perl-threads.x86_64 0:1.87-4.el7 will be installed
---> Package perl-threads-shared.x86_64 0:1.43-6.el7 will be installed
--> Running transaction check
---> Package perl-Encode.x86_64 0:2.51-7.el7 will be installed
---> Package perl-Pod-Escapes.noarch 1:1.04-291.el7 will be installed
---> Package perl-Pod-Usage.noarch 0:1.63-3.el7 will be installed
--> Processing Dependency: perl(Pod::Text) >= 3.15 for package: perl-Pod-Usage-1.63-3.el7.noarch
--> Processing Dependency: perl-Pod-Perldoc for package: perl-Pod-Usage-1.63-3.el7.noarch
---> Package perl-Text-ParseWords.noarch 0:3.29-4.el7 will be installed
--> Running transaction check
---> Package perl-Pod-Perldoc.noarch 0:3.20-4.el7 will be installed
--> Processing Dependency: perl(parent) for package: perl-Pod-Perldoc-3.20-4.el7.noarch
--> Processing Dependency: perl(HTTP::Tiny) for package: perl-Pod-Perldoc-3.20-4.el7.noarch
---> Package perl-podlators.noarch 0:2.5.1-3.el7 will be installed
--> Running transaction check
---> Package perl-HTTP-Tiny.noarch 0:0.033-3.el7 will be installed
---> Package perl-parent.noarch 1:0.225-244.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================
Installing for group install "PostgreSQL Database Server 10 PGDG":
postgresql10 x86_64 10.0-20170419_1PGDG.rhel7.7 pgdg10-updates-testing 1.4 M
postgresql10-contrib x86_64 10.0-20170419_1PGDG.rhel7.7 pgdg10-updates-testing 601 k
postgresql10-libs x86_64 10.0-20170419_1PGDG.rhel7.7 pgdg10-updates-testing 341 k
postgresql10-server x86_64 10.0-20170419_1PGDG.rhel7.7 pgdg10-updates-testing 4.5 M
Installing for dependencies:
libicu x86_64 50.1.2-15.el7 base 6.9 M
libxslt x86_64 1.1.28-5.el7 base 242 k
perl x86_64 4:5.16.3-291.el7 base 8.0 M
perl-Carp noarch 1.26-244.el7 base 19 k
perl-Encode x86_64 2.51-7.el7 base 1.5 M
perl-Exporter noarch 5.68-3.el7 base 28 k
perl-File-Path noarch 2.09-2.el7 base 26 k
perl-File-Temp noarch 0.23.01-3.el7 base 56 k
perl-Filter x86_64 1.49-3.el7 base 76 k
perl-Getopt-Long noarch 2.40-2.el7 base 56 k
perl-HTTP-Tiny noarch 0.033-3.el7 base 38 k
perl-PathTools x86_64 3.40-5.el7 base 82 k
perl-Pod-Escapes noarch 1:1.04-291.el7 base 51 k
perl-Pod-Perldoc noarch 3.20-4.el7 base 87 k
perl-Pod-Simple noarch 1:3.28-4.el7 base 216 k
perl-Pod-Usage noarch 1.63-3.el7 base 27 k
perl-Scalar-List-Utils x86_64 1.27-248.el7 base 36 k
perl-Socket x86_64 2.010-4.el7 base 49 k
perl-Storable x86_64 2.45-3.el7 base 77 k
perl-Text-ParseWords noarch 3.29-4.el7 base 14 k
perl-Time-HiRes x86_64 4:1.9725-3.el7 base 45 k
perl-Time-Local noarch 1.2300-2.el7 base 24 k
perl-constant noarch 1.27-2.el7 base 19 k
perl-libs x86_64 4:5.16.3-291.el7 base 688 k
perl-macros x86_64 4:5.16.3-291.el7 base 43 k
perl-parent noarch 1:0.225-244.el7 base 12 k
perl-podlators noarch 2.5.1-3.el7 base 112 k
perl-threads x86_64 1.87-4.el7 base 49 k
perl-threads-shared x86_64 1.43-6.el7 base 39 k
Transaction Summary
====================================================================================================================================
Install 4 Packages (+29 Dependent packages)
Total download size: 25 M
Installed size: 88 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/base/packages/perl-Carp-1.26-244.el7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for perl-Carp-1.26-244.el7.noarch.rpm is not installed
(1/33): perl-Carp-1.26-244.el7.noarch.rpm | 19 kB 00:00:06
(2/33): perl-Exporter-5.68-3.el7.noarch.rpm | 28 kB 00:00:00
(3/33): libxslt-1.1.28-5.el7.x86_64.rpm | 242 kB 00:00:09
(4/33): perl-File-Path-2.09-2.el7.noarch.rpm | 26 kB 00:00:05
(5/33): perl-Filter-1.49-3.el7.x86_64.rpm | 76 kB 00:00:00
(6/33): perl-File-Temp-0.23.01-3.el7.noarch.rpm | 56 kB 00:00:03
(7/33): perl-Getopt-Long-2.40-2.el7.noarch.rpm | 56 kB 00:00:00
(8/33): perl-PathTools-3.40-5.el7.x86_64.rpm | 82 kB 00:00:00
(9/33): perl-Pod-Escapes-1.04-291.el7.noarch.rpm | 51 kB 00:00:00
(10/33): perl-Pod-Perldoc-3.20-4.el7.noarch.rpm | 87 kB 00:00:00
(11/33): perl-Pod-Simple-3.28-4.el7.noarch.rpm | 216 kB 00:00:00
(12/33): perl-Pod-Usage-1.63-3.el7.noarch.rpm | 27 kB 00:00:00
(13/33): perl-Scalar-List-Utils-1.27-248.el7.x86_64.rpm | 36 kB 00:00:00
(14/33): perl-Socket-2.010-4.el7.x86_64.rpm | 49 kB 00:00:00
(15/33): perl-Storable-2.45-3.el7.x86_64.rpm | 77 kB 00:00:00
(16/33): perl-Text-ParseWords-3.29-4.el7.noarch.rpm | 14 kB 00:00:00
(17/33): perl-Time-HiRes-1.9725-3.el7.x86_64.rpm | 45 kB 00:00:00
(18/33): perl-Time-Local-1.2300-2.el7.noarch.rpm | 24 kB 00:00:00
(19/33): perl-constant-1.27-2.el7.noarch.rpm | 19 kB 00:00:00
(20/33): perl-libs-5.16.3-291.el7.x86_64.rpm | 688 kB 00:00:01
(21/33): perl-HTTP-Tiny-0.033-3.el7.noarch.rpm | 38 kB 00:00:06
(22/33): perl-macros-5.16.3-291.el7.x86_64.rpm | 43 kB 00:00:00
(23/33): perl-podlators-2.5.1-3.el7.noarch.rpm | 112 kB 00:00:00
(24/33): perl-parent-0.225-244.el7.noarch.rpm | 12 kB 00:00:01
(25/33): perl-threads-1.87-4.el7.x86_64.rpm | 49 kB 00:00:00
(26/33): perl-threads-shared-1.43-6.el7.x86_64.rpm | 39 kB 00:00:00
(27/33): libicu-50.1.2-15.el7.x86_64.rpm | 6.9 MB 00:00:21
(28/33): perl-Encode-2.51-7.el7.x86_64.rpm | 1.5 MB 00:00:27
(29/33): postgresql10-contrib-10.0-20170419_1PGDG.rhel7.7.x86_64.rpm | 601 kB 00:00:13
(30/33): perl-5.16.3-291.el7.x86_64.rpm | 8.0 MB 00:00:41
(31/33): postgresql10-libs-10.0-20170419_1PGDG.rhel7.7.x86_64.rpm | 341 kB 00:00:19
(32/33): postgresql10-10.0-20170419_1PGDG.rhel7.7.x86_64.rpm | 1.4 MB 00:01:11
(33/33): postgresql10-server-10.0-20170419_1PGDG.rhel7.7.x86_64.rpm | 4.5 MB 00:00:52
------------------------------------------------------------------------------------------------------------------------------------
Total 244 kB/s | 25 MB 00:01:46
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
Package : centos-release-7-2.1511.el7.centos.2.10.x86_64 (@anaconda)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : postgresql10-libs-10.0-20170419_1PGDG.rhel7.7.x86_64 1/33
Installing : postgresql10-10.0-20170419_1PGDG.rhel7.7.x86_64 2/33
Installing : 1:perl-parent-0.225-244.el7.noarch 3/33
Installing : perl-HTTP-Tiny-0.033-3.el7.noarch 4/33
Installing : perl-podlators-2.5.1-3.el7.noarch 5/33
Installing : perl-Pod-Perldoc-3.20-4.el7.noarch 6/33
Installing : 1:perl-Pod-Escapes-1.04-291.el7.noarch 7/33
Installing : perl-Encode-2.51-7.el7.x86_64 8/33
Installing : perl-Text-ParseWords-3.29-4.el7.noarch 9/33
Installing : perl-Pod-Usage-1.63-3.el7.noarch 10/33
Installing : 4:perl-libs-5.16.3-291.el7.x86_64 11/33
Installing : 4:perl-macros-5.16.3-291.el7.x86_64 12/33
Installing : 4:perl-Time-HiRes-1.9725-3.el7.x86_64 13/33
Installing : perl-constant-1.27-2.el7.noarch 14/33
Installing : perl-Time-Local-1.2300-2.el7.noarch 15/33
Installing : perl-Socket-2.010-4.el7.x86_64 16/33
Installing : perl-Carp-1.26-244.el7.noarch 17/33
Installing : perl-Storable-2.45-3.el7.x86_64 18/33
Installing : perl-PathTools-3.40-5.el7.x86_64 19/33
Installing : perl-Scalar-List-Utils-1.27-248.el7.x86_64 20/33
Installing : 1:perl-Pod-Simple-3.28-4.el7.noarch 21/33
Installing : perl-Exporter-5.68-3.el7.noarch 22/33
Installing : perl-File-Temp-0.23.01-3.el7.noarch 23/33
Installing : perl-File-Path-2.09-2.el7.noarch 24/33
Installing : perl-threads-shared-1.43-6.el7.x86_64 25/33
Installing : perl-threads-1.87-4.el7.x86_64 26/33
Installing : perl-Filter-1.49-3.el7.x86_64 27/33
Installing : perl-Getopt-Long-2.40-2.el7.noarch 28/33
Installing : 4:perl-5.16.3-291.el7.x86_64 29/33
Installing : libicu-50.1.2-15.el7.x86_64 30/33
Installing : libxslt-1.1.28-5.el7.x86_64 31/33
Installing : postgresql10-contrib-10.0-20170419_1PGDG.rhel7.7.x86_64 32/33
Installing : postgresql10-server-10.0-20170419_1PGDG.rhel7.7.x86_64 33/33
Verifying : postgresql10-server-10.0-20170419_1PGDG.rhel7.7.x86_64 1/33
Verifying : perl-HTTP-Tiny-0.033-3.el7.noarch 2/33
Verifying : perl-threads-shared-1.43-6.el7.x86_64 3/33
Verifying : 4:perl-Time-HiRes-1.9725-3.el7.x86_64 4/33
Verifying : postgresql10-10.0-20170419_1PGDG.rhel7.7.x86_64 5/33
Verifying : perl-constant-1.27-2.el7.noarch 6/33
Verifying : perl-PathTools-3.40-5.el7.x86_64 7/33
Verifying : 1:perl-Pod-Escapes-1.04-291.el7.noarch 8/33
Verifying : 1:perl-parent-0.225-244.el7.noarch 9/33
Verifying : postgresql10-contrib-10.0-20170419_1PGDG.rhel7.7.x86_64 10/33
Verifying : perl-File-Temp-0.23.01-3.el7.noarch 11/33
Verifying : 1:perl-Pod-Simple-3.28-4.el7.noarch 12/33
Verifying : perl-Time-Local-1.2300-2.el7.noarch 13/33
Verifying : perl-Pod-Perldoc-3.20-4.el7.noarch 14/33
Verifying : perl-Socket-2.010-4.el7.x86_64 15/33
Verifying : perl-Carp-1.26-244.el7.noarch 16/33
Verifying : libxslt-1.1.28-5.el7.x86_64 17/33
Verifying : perl-Storable-2.45-3.el7.x86_64 18/33
Verifying : perl-Scalar-List-Utils-1.27-248.el7.x86_64 19/33
Verifying : 4:perl-libs-5.16.3-291.el7.x86_64 20/33
Verifying : 4:perl-macros-5.16.3-291.el7.x86_64 21/33
Verifying : postgresql10-libs-10.0-20170419_1PGDG.rhel7.7.x86_64 22/33
Verifying : perl-Pod-Usage-1.63-3.el7.noarch 23/33
Verifying : perl-Encode-2.51-7.el7.x86_64 24/33
Verifying : perl-Exporter-5.68-3.el7.noarch 25/33
Verifying : perl-podlators-2.5.1-3.el7.noarch 26/33
Verifying : perl-Getopt-Long-2.40-2.el7.noarch 27/33
Verifying : perl-File-Path-2.09-2.el7.noarch 28/33
Verifying : 4:perl-5.16.3-291.el7.x86_64 29/33
Verifying : libicu-50.1.2-15.el7.x86_64 30/33
Verifying : perl-threads-1.87-4.el7.x86_64 31/33
Verifying : perl-Filter-1.49-3.el7.x86_64 32/33
Verifying : perl-Text-ParseWords-3.29-4.el7.noarch 33/33
Installed:
postgresql10.x86_64 0:10.0-20170419_1PGDG.rhel7.7 postgresql10-contrib.x86_64 0:10.0-20170419_1PGDG.rhel7.7
postgresql10-libs.x86_64 0:10.0-20170419_1PGDG.rhel7.7 postgresql10-server.x86_64 0:10.0-20170419_1PGDG.rhel7.7
Dependency Installed:
libicu.x86_64 0:50.1.2-15.el7 libxslt.x86_64 0:1.1.28-5.el7 perl.x86_64 4:5.16.3-291.el7
perl-Carp.noarch 0:1.26-244.el7 perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7
perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7 perl-Filter.x86_64 0:1.49-3.el7
perl-Getopt-Long.noarch 0:2.40-2.el7 perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-PathTools.x86_64 0:3.40-5.el7
perl-Pod-Escapes.noarch 1:1.04-291.el7 perl-Pod-Perldoc.noarch 0:3.20-4.el7 perl-Pod-Simple.noarch 1:3.28-4.el7
perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-4.el7
perl-Storable.x86_64 0:2.45-3.el7 perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7
perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7 perl-libs.x86_64 4:5.16.3-291.el7
perl-macros.x86_64 4:5.16.3-291.el7 perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7
perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7
Complete!
[root@localhost home]#
3.初始化数据库
[root@localhost home]# /usr/pgsql-10/bin/postgresql10-setup initdb
Initializing database ... OK
[root@localhost home]#
4.启动数据库
[root@localhost home]# systemctl start postgresql-10.service
[root@localhost home]#
5.开机启动
[root@localhost home]# systemctl enable postgresql-10.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-10.service to /usr/lib/systemd/system/postgresql-10.service.
[root@localhost home]#
6.登陆验证
[root@localhost home]# su - postgres
-bash-4.2$ psql
psql (10devel)
Type "help" for help.
postgres=# \dt
No relations found.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
postgres=# \q
7.数据目录
-bash-4.2$ /usr/pgsql-10/bin/pg_ctl status
pg_ctl: server is running (PID: 2630)
/usr/pgsql-10/bin/postgres "-D" "/var/lib/pgsql/10/data/"
-bash-4.2$
8.创建分区表
-bash-4.2$ /usr/pgsql-10/bin/psql
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \dt
No relations found.
testdb=#
testdb=# CREATE TABLE part_tab (id bigserial not null, created_at timestamp(0) without time zone,name varchar(20)) PARTITION BY RANGE (created_at);
CREATE TABLE
testdb=# CREATE TABLE part_2017_04_20 PARTITION OF part_tab FOR VALUES FROM (‘2017-04-20‘) TO (‘2017-04-21‘);
CREATE TABLE
testdb=# CREATE TABLE part_2017_04_21 PARTITION OF part_tab FOR VALUES FROM (‘2017-04-21‘) TO (‘2017-04-22‘);
CREATE TABLE
testdb=# insert into part_tab(created_at,name) values(‘2017-04-20 17:42:08‘,‘David‘);
INSERT 0 1
testdb=# insert into part_tab(created_at,name) values(‘2017-04-21 09:36:50‘,‘Peter‘);
INSERT 0 1
testdb=# select * from part_tab;
id | created_at | name
----+---------------------+-------
1 | 2017-04-20 17:42:08 | David
2 | 2017-04-21 09:36:50 | Peter
(2 rows)
testdb=# select * from part_2017_04_20;
id | created_at | name
----+---------------------+-------
1 | 2017-04-20 17:42:08 | David
(1 row)
testdb=# select * from part_2017_04_21;
id | created_at | name
----+---------------------+-------
2 | 2017-04-21 09:36:50 | Peter
(1 row)
testdb=# \d+ part_
part_2017_04_20 part_2017_04_21 part_tab part_tab_id_seq
testdb=# \d+ part_tab;
Table "public.part_tab"
Column | Type | Collation | Nullable | Default | Storage | Stats tar
get | Description
------------+--------------------------------+-----------+----------+--------------------------------------+----------+----------
----+-------------
id | bigint | | not null | nextval(‘part_tab_id_seq‘::regclass) | plain |
|
created_at | timestamp(0) without time zone | | not null | | plain |
|
name | character varying(20) | | | | extended |
|
Partition key: RANGE (created_at)
Partitions: part_2017_04_20 FOR VALUES FROM (‘2017-04-20 00:00:00‘) TO (‘2017-04-21 00:00:00‘),
part_2017_04_21 FOR VALUES FROM (‘2017-04-21 00:00:00‘) TO (‘2017-04-22 00:00:00‘)
testdb=#
testdb=# explain analyze select * from part_tab where created_at between ‘2017-04-21‘ and ‘2017-04-22‘;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
----------------------------
Append (cost=0.00..22.00 rows=4 width=74) (actual time=0.016..0.017 rows=1 loops=1)
-> Seq Scan on part_2017_04_21 (cost=0.00..22.00 rows=4 width=74) (actual time=0.015..0.015 rows=1 loops=1)
Filter: ((created_at >= ‘2017-04-21 00:00:00‘::timestamp without time zone) AND (created_at <= ‘2017-04-22 00:00:00‘::ti
mestamp without time zone))
Planning time: 0.832 ms
Execution time: 0.055 ms
(5 rows)
testdb=# explain analyze select * from part_tab where created_at between ‘2017-04-20‘ and ‘2017-04-21‘;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
----------------------------
Append (cost=0.00..44.00 rows=8 width=74) (actual time=0.019..0.023 rows=1 loops=1)
-> Seq Scan on part_2017_04_20 (cost=0.00..22.00 rows=4 width=74) (actual time=0.019..0.019 rows=1 loops=1)
Filter: ((created_at >= ‘2017-04-20 00:00:00‘::timestamp without time zone) AND (created_at <= ‘2017-04-21 00:00:00‘::ti
mestamp without time zone))
-> Seq Scan on part_2017_04_21 (cost=0.00..22.00 rows=4 width=74) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((created_at >= ‘2017-04-20 00:00:00‘::timestamp without time zone) AND (created_at <= ‘2017-04-21 00:00:00‘::ti
mestamp without time zone))
Rows Removed by Filter: 1
Planning time: 0.199 ms
Execution time: 0.052 ms
(8 rows)
testdb=#
本文出自 “yiyi” 博客，请务必保留此出处http://heyiyi.blog.51cto.com/205455/1917895
PostgreSQL 10 build-in table partition(Range)