PostgreSQL: up and running
Gespeichert in:
| Hauptverfasser: | , |
|---|---|
| Format: | Buch |
| Sprache: | English |
| Veröffentlicht: |
Beijing [u.a.]
O'Reilly
2012
|
| Ausgabe: | 1.ed. |
| Schriftenreihe: | A practical guide to the advanced open source database
|
| Schlagworte: | |
| Online-Zugang: | Inhaltsverzeichnis Klappentext |
| Beschreibung: | XIV, 147 S. |
| ISBN: | 9781449326333 |
Internformat
MARC
| LEADER | 00000nam a2200000 c 4500 | ||
|---|---|---|---|
| 001 | BV041474268 | ||
| 003 | DE-604 | ||
| 005 | 20211221 | ||
| 007 | t | ||
| 008 | 131211s2012 |||| 00||| eng d | ||
| 020 | |a 9781449326333 |9 978-1-449-32633-3 | ||
| 024 | 3 | |a 9781449326333 | |
| 035 | |a (OCoLC)806030894 | ||
| 035 | |a (DE-599)BVBBV041474268 | ||
| 040 | |a DE-604 |b ger |e rakddb | ||
| 041 | 0 | |a eng | |
| 049 | |a DE-739 |a DE-83 | ||
| 084 | |a ST 271 |0 (DE-625)143639: |2 rvk | ||
| 084 | |a 004 |2 sdnb | ||
| 100 | 1 | |a Obe, Regina O. |e Verfasser |0 (DE-588)1032811145 |4 aut | |
| 245 | 1 | 0 | |a PostgreSQL |b up and running |c Regina Obe and Leo Hsu |
| 250 | |a 1.ed. | ||
| 264 | 1 | |a Beijing [u.a.] |b O'Reilly |c 2012 | |
| 300 | |a XIV, 147 S. | ||
| 336 | |b txt |2 rdacontent | ||
| 337 | |b n |2 rdamedia | ||
| 338 | |b nc |2 rdacarrier | ||
| 490 | 0 | |a A practical guide to the advanced open source database | |
| 650 | 0 | 7 | |a PostgreSQL |0 (DE-588)4690455-4 |2 gnd |9 rswk-swf |
| 689 | 0 | 0 | |a PostgreSQL |0 (DE-588)4690455-4 |D s |
| 689 | 0 | |5 DE-604 | |
| 700 | 1 | |a Hsu, Leo S. |e Verfasser |0 (DE-588)1032811420 |4 aut | |
| 856 | 4 | 2 | |m Digitalisierung UB Passau - ADAM Catalogue Enrichment |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=026920349&sequence=000003&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |3 Inhaltsverzeichnis |
| 856 | 4 | 2 | |m Digitalisierung UB Passau - ADAM Catalogue Enrichment |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=026920349&sequence=000004&line_number=0002&func_code=DB_RECORDS&service_type=MEDIA |3 Klappentext |
| 999 | |a oai:aleph.bib-bvb.de:BVB01-026920349 | ||
Datensatz im Suchindex
| _version_ | 1804151612823830528 |
|---|---|
| adam_text | Table
of
Contents
Preface
.....................................................................ix
1.
The Basics
............................................................. 1
Where to Get PostgreSQL
1
Notable PostgreSQL Forks
1
Administration Tools
2
What s New in Latest Versions of PostgreSQL?
3
Why Upgrade?
4
What to Look for in PostgreSQL
9.2 4
PostgreSQL
9.1
Improvements
5
Database Drivers
5
Server and Database Objects
6
Where to Get Help
8
2.
Database Administration
.................................................9
Configuration Files
9
The postgresql.conf File
10
The pg_hba.conf File
12
Reload the Configuration Files
14
Setting Up Groups and Login Roles (Users)
14
Creating an Account That Can Log In
15
Creating Group Roles
15
Roles Inheriting Rights
16
Databases and Management
16
Creating and Using a Template Database
16
Organizing Your Database Using
Schemas 17
Permissions
17
Extensions and Contribs
18
Installing Extensions
20
Common Extensions
21
Backup
23
Ш
Selective Backup
Using pg_dump
23
Systemwide Backup
Using
pg_dumpalł
24
Restore
25
Terminating Connections
25
Using psql to Restore Plain Text SQL backups
26
Using pg_j:estore
26
Managing Disk Space with Tablespaces
27
Creating Tablespaces
28
Moving Objects Between Tablespaces
28
Verboten 28
Delete PostgreSQL Core System Files and Binaries
28
Giving Full Administrative Rights to the
Postgres
System (Daemon) Ac¬
count
29
Setting shared_buffers Too High
29
Trying to Start PostgreSQL on a Port Already in Use
30
3.
psql
................................................................. 31
Interactive psql
31
Non-Interactive psql
32
Session Configurations
33
Changing Prompts
35
Timing Details
35
AUTOCOMMIT
35
Shortcuts
36
Retrieving Prior Commands
36
psql Gems
37
Executing Shell Commands
37
Lists and Structures
37
Importing and Exporting Data
38
Basic Reporting
39
4.
Using pgAdmin
........................................................43
Getting Started
43
Overview of Features
43
Connecting to a PostgreSQL server
44
Navigating pgAdmin
44
pgAdmin Features
45
Accessing psql from pgAdmin
46
Editing postgresql.conf and pg_hba.conf from pgAdmin
47
Creating Databases and Setting Permissions
47
Backup and Restore
48
pgScript
51
Graphical Explain
54
iv
I Table of Contents
Job Scheduling with pgAgent
55
Installing pgAgent
56
Scheduling Jobs
56
Helpful Queries
57
5.
Data Types
............................................................59
Numeric Data Types
59
Serial
59
Generate Series Function
60
Arrays
60
Array Constructors
61
Referencing Elements in An Array
61
Array Slicing and Splicing
62
Character Types
62
String Functions
63
Splitting Strings into Arrays, Tables, or Substrings
63
Regular Expressions and Pattern Matching
64
Temporal Data Types
65
Time Zones: What It Is and What It Isn t
67
Operators and Functions for Date and Time Data Types
69
XML
70
Loading XML Data
71
Querying XML Data
71
Custom and Composite Data Types
71
All Tables Are Custom
72
Building Your Own Custom Type
72
6.
Of Tables, Constraints, and Indexes
....................................... 75
Tables
75
Table Creation
75
Multi-Row Insert
77
An Elaborate Insert
77
Constraints
79
Foreign Key Constraints
79
Unique Constraints
80
Check Constraints
80
Exclusion. Constraints
81
Indexes
81
PostgreSQL Stock Indexes
81
Operator Class
83
Functional Indexes
84
Partial Indexes
84
Multicolumn Indexes
85
Table of Contents
7.
SQL: The PostgreSQL Way
............................................... 87
SQL Views
87
Window Functions
89
Partition By
90
Order By
91
Common Table Expressions
92
Standard
СТЕ
93
Writeable CTEs
94
Recursive
СТЕ
94
Constructions Unique to PostgreSQL
95
DISTINCT ON
95
LIMIT and OFFSET
96
Shorthand Casting
96
ILIKE
for Case Insensitive Search
96
Set Returning Functions in SELECT
97
Selective DELETE, UPDATE, and SELECT from Inherited Tables
97
RETURNING Changed Records
98
Composite Types in Queries
98
8.
Writing Functions
.....................................................101
Anatomy of PostgreSQL Functions
101
Function Basics
101
Trusted and Untrusted Languages
102
Writing Functions with SQL
103
Writing PL/pgSQL Functions
105
Writing PL/Python Functions
105
Basic Python Function
106
Trigger Functions
107
Aggregates
109
9.
Query Performance Tuning
.............................................113
EXPLAIN and EXPLAIN ANALYZE
113
Writing Better Queries
115
Overusing Subqueries in SELECT
116
Avoid SELECT*
118
Make Good Use of CASE
118
Guiding the Query Planner
120
Strategy Settings
120
How Useful Is Your Index?
120
Table Stats
122
Random Page Cost and Quality of Drives
122
Caching
123
vi
I Table of Contents
10.
Replication and External Data
...........................................125
Replication Overview
125
Replication Lingo
125
PostgreSQL Built-in Replication Advancements
126
Third-Party Replication Options
127
Setting Up Replication
127
Configuring the Master
127
Configuring the Slaves
128
Initiate the Replication Process
129
Foreign Data Wrappers (FDW)
130
Querying Simple Flat File Data Sources
130
Querying More Complex Data Sources
131
Appendix: Install, Hosting, and Command-Line Guides
............................133
Table of Contents I
vii
PostgreSQL: Up and Running
If you re thinking about migrating to the PostgreSQL open source
database system, this guide provides a concise overview to help you
quickly understand and use PostgreSQL s unique features. Not only will
you learn about the enterprise class features in the
9-2
release, you ll also
discover that PostgeSQL is more than just a database system
—
it s also an
impressive application platform.
With numerous examples throughout this book, you ll learn how to
achieve tasks that are difficult or impossible in other databases. If you re
an existing PostgreSQL user, you ll pick up gems you may have missed
along the way.
■
Learn basic administration tasks, such as role management,
database creation, backup, and restore
■
Apply the psql command-line utility and the pgAdmin graphical
administration tool
■
Explore PostgreSQL tables, constraints, and indexes
■
Learn powerful SQL constructs not generally found in other
databases
■
Use several different languages to write database functions
■
Tune your queries to run as fast as your hardware will allow
■
Query external and variegated data sources with Foreign Data
Wrappers
■
Learn how to replicate data, using built-in replication features
Purchase the ebook edition of this O Reilly title at oreilly.com and get
free updates for the life of the edition. Our ebooks are optimized for
several electronic formats, including PDF, EPUB,
Mobi,
APK,
and
DAISY—all DRM-free.
|
| any_adam_object | 1 |
| author | Obe, Regina O. Hsu, Leo S. |
| author_GND | (DE-588)1032811145 (DE-588)1032811420 |
| author_facet | Obe, Regina O. Hsu, Leo S. |
| author_role | aut aut |
| author_sort | Obe, Regina O. |
| author_variant | r o o ro roo l s h ls lsh |
| building | Verbundindex |
| bvnumber | BV041474268 |
| classification_rvk | ST 271 |
| ctrlnum | (OCoLC)806030894 (DE-599)BVBBV041474268 |
| discipline | Informatik |
| edition | 1.ed. |
| format | Book |
| fullrecord | <?xml version="1.0" encoding="UTF-8"?><collection xmlns="http://www.loc.gov/MARC21/slim"><record><leader>01673nam a2200385 c 4500</leader><controlfield tag="001">BV041474268</controlfield><controlfield tag="003">DE-604</controlfield><controlfield tag="005">20211221 </controlfield><controlfield tag="007">t</controlfield><controlfield tag="008">131211s2012 |||| 00||| eng d</controlfield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">9781449326333</subfield><subfield code="9">978-1-449-32633-3</subfield></datafield><datafield tag="024" ind1="3" ind2=" "><subfield code="a">9781449326333</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(OCoLC)806030894</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(DE-599)BVBBV041474268</subfield></datafield><datafield tag="040" ind1=" " ind2=" "><subfield code="a">DE-604</subfield><subfield code="b">ger</subfield><subfield code="e">rakddb</subfield></datafield><datafield tag="041" ind1="0" ind2=" "><subfield code="a">eng</subfield></datafield><datafield tag="049" ind1=" " ind2=" "><subfield code="a">DE-739</subfield><subfield code="a">DE-83</subfield></datafield><datafield tag="084" ind1=" " ind2=" "><subfield code="a">ST 271</subfield><subfield code="0">(DE-625)143639:</subfield><subfield code="2">rvk</subfield></datafield><datafield tag="084" ind1=" " ind2=" "><subfield code="a">004</subfield><subfield code="2">sdnb</subfield></datafield><datafield tag="100" ind1="1" ind2=" "><subfield code="a">Obe, Regina O.</subfield><subfield code="e">Verfasser</subfield><subfield code="0">(DE-588)1032811145</subfield><subfield code="4">aut</subfield></datafield><datafield tag="245" ind1="1" ind2="0"><subfield code="a">PostgreSQL</subfield><subfield code="b">up and running</subfield><subfield code="c">Regina Obe and Leo Hsu</subfield></datafield><datafield tag="250" ind1=" " ind2=" "><subfield code="a">1.ed.</subfield></datafield><datafield tag="264" ind1=" " ind2="1"><subfield code="a">Beijing [u.a.]</subfield><subfield code="b">O'Reilly</subfield><subfield code="c">2012</subfield></datafield><datafield tag="300" ind1=" " ind2=" "><subfield code="a">XIV, 147 S.</subfield></datafield><datafield tag="336" ind1=" " ind2=" "><subfield code="b">txt</subfield><subfield code="2">rdacontent</subfield></datafield><datafield tag="337" ind1=" " ind2=" "><subfield code="b">n</subfield><subfield code="2">rdamedia</subfield></datafield><datafield tag="338" ind1=" " ind2=" "><subfield code="b">nc</subfield><subfield code="2">rdacarrier</subfield></datafield><datafield tag="490" ind1="0" ind2=" "><subfield code="a">A practical guide to the advanced open source database</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">PostgreSQL</subfield><subfield code="0">(DE-588)4690455-4</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="689" ind1="0" ind2="0"><subfield code="a">PostgreSQL</subfield><subfield code="0">(DE-588)4690455-4</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2=" "><subfield code="5">DE-604</subfield></datafield><datafield tag="700" ind1="1" ind2=" "><subfield code="a">Hsu, Leo S.</subfield><subfield code="e">Verfasser</subfield><subfield code="0">(DE-588)1032811420</subfield><subfield code="4">aut</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">Digitalisierung UB Passau - ADAM Catalogue Enrichment</subfield><subfield code="q">application/pdf</subfield><subfield code="u">http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=026920349&sequence=000003&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Inhaltsverzeichnis</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">Digitalisierung UB Passau - ADAM Catalogue Enrichment</subfield><subfield code="q">application/pdf</subfield><subfield code="u">http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=026920349&sequence=000004&line_number=0002&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Klappentext</subfield></datafield><datafield tag="999" ind1=" " ind2=" "><subfield code="a">oai:aleph.bib-bvb.de:BVB01-026920349</subfield></datafield></record></collection> |
| id | DE-604.BV041474268 |
| illustrated | Not Illustrated |
| indexdate | 2024-07-10T00:57:36Z |
| institution | BVB |
| isbn | 9781449326333 |
| language | English |
| oai_aleph_id | oai:aleph.bib-bvb.de:BVB01-026920349 |
| oclc_num | 806030894 |
| open_access_boolean | |
| owner | DE-739 DE-83 |
| owner_facet | DE-739 DE-83 |
| physical | XIV, 147 S. |
| publishDate | 2012 |
| publishDateSearch | 2012 |
| publishDateSort | 2012 |
| publisher | O'Reilly |
| record_format | marc |
| series2 | A practical guide to the advanced open source database |
| spelling | Obe, Regina O. Verfasser (DE-588)1032811145 aut PostgreSQL up and running Regina Obe and Leo Hsu 1.ed. Beijing [u.a.] O'Reilly 2012 XIV, 147 S. txt rdacontent n rdamedia nc rdacarrier A practical guide to the advanced open source database PostgreSQL (DE-588)4690455-4 gnd rswk-swf PostgreSQL (DE-588)4690455-4 s DE-604 Hsu, Leo S. Verfasser (DE-588)1032811420 aut Digitalisierung UB Passau - ADAM Catalogue Enrichment application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=026920349&sequence=000003&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA Inhaltsverzeichnis Digitalisierung UB Passau - ADAM Catalogue Enrichment application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=026920349&sequence=000004&line_number=0002&func_code=DB_RECORDS&service_type=MEDIA Klappentext |
| spellingShingle | Obe, Regina O. Hsu, Leo S. PostgreSQL up and running PostgreSQL (DE-588)4690455-4 gnd |
| subject_GND | (DE-588)4690455-4 |
| title | PostgreSQL up and running |
| title_auth | PostgreSQL up and running |
| title_exact_search | PostgreSQL up and running |
| title_full | PostgreSQL up and running Regina Obe and Leo Hsu |
| title_fullStr | PostgreSQL up and running Regina Obe and Leo Hsu |
| title_full_unstemmed | PostgreSQL up and running Regina Obe and Leo Hsu |
| title_short | PostgreSQL |
| title_sort | postgresql up and running |
| title_sub | up and running |
| topic | PostgreSQL (DE-588)4690455-4 gnd |
| topic_facet | PostgreSQL |
| url | http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=026920349&sequence=000003&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=026920349&sequence=000004&line_number=0002&func_code=DB_RECORDS&service_type=MEDIA |
| work_keys_str_mv | AT obereginao postgresqlupandrunning AT hsuleos postgresqlupandrunning |