You are previewing Learning Heroku Postgres.
O'Reilly logo
Learning Heroku Postgres

Book Description

Efficiently design, implement, and manage a successful PostgreSQL database with Heroku

In Detail

This book establishes a good learning mechanism by initially covering simple topics such as Heroku Toolbelt before moving on to complex topics such as data caching and tuning and PostGIS.

The book begins by giving you an introduction to the working of Heroku Postgres. This is followed by an overview of the PostgreSQL add-on, using which you'll create your first database and connect it using various programming languages such as Java, Ruby, Python, and Node.js. You will then look at securing your data, monitoring, working with permissions, and generating backups along with database rollbacks, followers, and forks to manage any loss in your data. Finally, you will improve the performance of your database by diving into log statements and understand how to avoid common mistakes.

What You Will Learn

  • Introduce yourself to the inner workings of Heroku Postgres

  • Deploy your first application with Heroku using the Heroku Toolbelt

  • Work with permission levels to connect your database with a number of programming languages

  • Secure your database using Postgres backups

  • Interact with your data and share it with Dataclips

  • Manage your data loss by performing rollbacks and working with followers and forks

  • Identify common errors by analyzing logs and viewing metrics

  • Add functionalities to your database with extensions such as PostGIS and Full Text Search Dictionaries

  • Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

    Table of Contents

    1. Learning Heroku Postgres
      1. Table of Contents
      2. Learning Heroku Postgres
      3. Credits
      4. About the Author
      5. About the Reviewers
      6. www.PacktPub.com
        1. Support files, eBooks, discount offers, and more
          1. Why subscribe?
          2. Free access for Packt account holders
          3. Instant updates on new Packt books
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      8. 1. Getting Started with Heroku Postgres
        1. How does Heroku work?
          1. Heroku Dashboard and Heroku Toolbelt
          2. Deploying your applications
          3. Heroku architecture
          4. HTTP routing
          5. Dyno Manager
          6. Config vars
          7. Understanding the Dynos
          8. Workers
          9. Add-ons
          10. Logplex
          11. Heroku API
        2. Postgres supported versions
        3. Choosing the right Heroku Postgres plan
          1. Shared features
        4. Production-tier technical characterization
        5. High availability
        6. Self-test Questions
        7. Summary
      9. 2. Heroku Toolbelt
        1. Signing up
        2. Installing the Heroku Toolbelt
        3. Logging in to Heroku
        4. Deploying an application
        5. The sample application source code
        6. The dependencies file
        7. Deploying the sample application
        8. Visiting the sample application
        9. Self-test questions
        10. Summary
      10. 3. Postgres Add-on
        1. Local setup
          1. Setting up PostgreSQL on Mac OS X
          2. Setting up PostgreSQL on Windows
          3. Setting up PostgreSQL on Linux
          4. Creating a local database
        2. Creating a new app
          1. Via the Heroku dashboard
          2. Via the Heroku client
        3. Adding the add-on and creating the first database
          1. Adding the Heroku Postgres add-on
            1. Via the Heroku add-ons gallery
            2. Via the Heroku client
          2. Creating the first database
        4. Heroku Postgres web interface
        5. Using the Heroku client with Postgres
          1. The pg:info command
          2. The pg:psql command
          3. The pg:push command
          4. The pg:pull command
          5. The pg:ps command
          6. The pg:kill command
          7. The pg:killall command
          8. The pg:promote command
          9. The pg:credentials command
          10. The pg:reset command
        6. Connecting with Java
          1. JDBC
          2. Spring/XML
          3. Connecting with Ruby
        7. Connecting with Python
        8. Connecting with Django
        9. Connecting with Node.js
        10. Connection permissions
        11. Multiple schemas
        12. External connections
        13. Connecting to databases from outside Heroku
        14. Monitoring and logging
        15. Removing the add-on
        16. Self-test Questions
        17. Summary
      11. 4. PG Backups
        1. Plans
          1. Installing the add-on
            1. Via the Heroku client
            2. Via the web interface
          2. Changing the plan
          3. Creating a backup
            1. Listing backups
              1. Via the Heroku client
              2. Via the web interface
          4. Downloading a backup
          5. Deleting backups
          6. Restoring from a backup
          7. Importing databases
        2. Importing in Heroku Postgres
        3. Exporting databases
        4. Downloading the backup
        5. Restoring to local database
        6. Removing the add-on
          1. Via the Heroku client
          2. Via the web interface
        7. Self-test Questions
        8. Summary
      12. 5. Dataclips
        1. Creating dataclips
        2. Sharing dataclips
        3. Interacting with dataclips
          1. Data refresh
        4. Dataclips security
        5. Limitations and additional settings
        6. Self-test questions
        7. Summary
      13. 6. Rollback, Followers, and Forks
        1. Heroku Postgres rollback
          1. Checking the rollback feature
          2. Available period
          3. Creating a rollback database
          4. Promote a rollback database
          5. Deprovisioning a rollback database
        2. Heroku Postgres follower databases
          1. Creating and managing follower databases
          2. Unfollow the main database
          3. Upgrade the database plan with follower
          4. High availability with followers
        3. Heroku Postgres forking databases
          1. Forking your database
          2. Forking databases with the fast option
          3. View your fork databases
          4. Deprovisioning a fork database
        4. Self-test questions
        5. Summary
      14. 7. Understanding Log Statements and Common Errors
        1. Log statements
        2. Common errors
          1. LOG: long duration
          2. LOG: unexpected EOF on client connection
          3. PGError: permission denied for relation
          4. PGError: operator does not exist
          5. PGError: relation "table-name" does not exist
          6. PGError: column "column-name" cannot...
          7. PGError: SSL SYSCALL error: EOF detected
          8. PGError: prepared statement "a30" already exists
          9. FATAL: too many connections for role
          10. FATAL: could not receive data...
          11. FATAL: role "role-name"...
          12. FATAL: terminating connection due to administrator command
          13. FATAL: remaining connection slots are reserved for non-replication superuser connections
          14. Temporary file: path "file path", size "file size"
        3. Metrics logs
        4. Self-test questions
        5. Summary
      15. 8. Extensions, PostGIS, Full Text Search Dictionaries, Data Caching, and Tuning
        1. Heroku Postgres extensions
        2. Data types
          1. Case-insensitive text – citext
          2. Cube
          3. HStore
          4. Label tree – ltree
          5. Product numbering – isn
          6. Functions
          7. Earth distance
          8. Intarray
          9. Fuzzy match – fuzzystrmatch
          10. PGCrypto
          11. Table functions and pivot tables – tablefunc
          12. Trigram – pg_trgm
          13. UUID generation
        3. Statistics
          1. Row locking – pgrowlocks
          2. Tuple statistics – pgstattuple
          3. Index types
          4. Btree GiST – btree_gist
        4. Languages
          1. PLV8 – V8 Engine JavaScript Procedural Language
        5. Full text search dictionaries
          1. Dict int
          2. Unaccent
        6. Data caching
        7. Setting up PostGIS
          1. Provisioning
        8. Database tuning
          1. Database VACUUM
          2. Determining the bloat factor
          3. Manual vacuuming
        9. Automatic vacuuming
        10. Performance analysis
        11. Self-test Questions
        12. Summary
      16. A. Keyword List
      17. B. Self-test Answers
        1. Chapter 1: Getting Started with Heroku Postgres
        2. Chapter 2: Heroku Toolbelt
        3. Chapter 3: Postgres Add-on
        4. Chapter 4: PG Backups
        5. Chapter 5: Dataclips
        6. Chapter 6: Rollback, Followers, and Forks
        7. Chapter 7: Understanding Log Statements and Common Errors
        8. Chapter 8: Extensions, PostGIS, Full Text Search Dictionaries, Data Caching, and Tuning
      18. Index