O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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