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=# （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |