postgresql ::
PostgreSQL is a mature, stable, feature-rich database server. Unlike its fellow open-source contenders MySQL and Firebird, PostgreSQL has a steeper learning curve when you’re first getting started, but you can avoid most of the installation, configuration, and maintenance hassles if you use a cloud-hosted database service.
PostgreSQL是成熟，稳定，功能丰富的数据库服务器。 与其他开放源代码竞争者MySQL和Firebird不同，PostgreSQL刚入门时具有较陡的学习曲线，但是如果您使用云托管的数据库服务，则可以避免大多数安装，配置和维护麻烦。
Heroku Postgres is a reliable and powerful database-as-a-service based on PostgreSQL provided by Heroku. Hosting your data with Heroku lets you focus more on writing your application’s SQL rather than learning how to be a DBA.
Heroku Postgres是基于 Heroku提供的PostgreSQL的可靠，功能强大的数据库即服务 。 使用Heroku托管数据使您可以将更多精力放在编写应用程序SQL上，而不是学习如何成为一名DBA。
In this article I’ll show you how to get started with Heroku Postgres. I’ll share some of the benefits of using Heroku, show you how to sign up, and show you how easy it is to set up a hosted PostgreSQL database instance and connect to it from PHP using PDO.
在本文中，我将向您展示如何开始使用Heroku Postgres。 我将分享使用Heroku的一些好处，向您展示如何注册，并向您展示设置托管的PostgreSQL数据库实例并使用PDO从PHP连接到它的简便性。
When faced with a serious choice, it’s a good idea to weigh the pros and cons before making a final decision. At the core of almost any web-based application nowadays is its data, and so choosing the right database strategy is probably more important than most people realize. Heroku Postgres may not be right for you, but how do you know?
当面临一个认真的选择时，最好在做出最终决定之前权衡利弊。 如今，几乎所有基于Web的应用程序的核心都是数据，因此，选择正确的数据库策略可能比大多数人意识到的更为重要。 Heroku Postgres可能不适合您，但是您怎么知道？
Heroku Postgres is a tiered offering, the lowest of which is priced at $200 a month. If you’re a developer looking just to tinker around and learn something new, this might be a deal-breaker for you. (It would be nice if Heroku could offer some sort of limited free developer trial in the future so more people could learn to work with their offerings.) On the flip side of the coin, $200 is just a drop in a bucket for a successful web-based application. It’s definitely cheaper than running your own hardware and the personnel expense of maintaining the database and ensuring compliance with any relevant regulations.
Heroku Postgres是一种分层产品，其中最低的价格为每月200美元。 如果您只是一个开发者，只是想四处学习并学到新东西，那么这对您来说可能是个大难题。 (如果Heroku将来可以提供某种有限的免费开发人员试用版，这很好，那么更多的人可以学习使用他们的产品。)另一方面，200美元对于成功的人来说简直是杯水车薪。基于Web的应用程序。 它绝对比运行您自己的硬件便宜，并且比维护数据库和确保遵守任何相关法规的人员费用便宜。
It’s incredibly easy to create a database instance and you’re free to upgrade or downgrade your tier as your needs dictate. In fact, the ease of database creation with Heroku Postgres alleviates one of the oft-heard complaints about PostgreSQL — that it’s difficult to install. Heroku claims to be the largest PostgreSQL database-as-a-service provider. Given this scale, it makes sense to let the experts manage your database.
创建数据库实例非常容易，并且可以根据需要自由升级或降级您的层。 实际上，使用Heroku Postgres轻松创建数据库减轻了人们对PostgreSQL经常听到的抱怨之一，即安装困难。 Heroku声称是最大的PostgreSQL数据库即服务提供商。 在这种规模下，让专家来管理您的数据库是有意义的。
Heroku promises 99.99% uptime, and has done some impressive things to achieve that. Transactions are continually archived to a geographically distributed data store, and automated health-checks are performed every 30 seconds to ensure your database is available. You type one command, and Heroku literally takes care of the rest.
Heroku承诺99.99％的正常运行时间，并为此做出了一些令人印象深刻的事情。 事务被连续归档到地理分布的数据存储中，并且每30秒执行一次自动运行状况检查，以确保您的数据库可用。 键入一个命令，Heroku就会处理其余的所有内容。
Heroku allows almost unlimited data, so large datasets and data warehousing is no problem. Their plans are priced according to hot data set, the portion of your data that is accessed the most and stays resident in RAM for performance reasons. The lowest tier supports 1.7GB of hot data and the highest supports 68GB.
Heroku几乎允许无限的数据，因此大型数据集和数据仓库是没有问题的。 他们的计划是根据热门数据集定价的，这是您数据中访问量最大的部分，并且出于性能原因而保留在RAM中。 最低层支持1.7GB的热数据，最高层支持68GB。
Signing up for the Heroku Postgres service is easy. Just go to postgres.heroku.com in your browser and click the Sign Up Now button in the middle of the page. If you already have an existing Heroku account you can simply provide the email address and password you’ve used for it and you will be logged in. Otherwise, you can create a new account now.
注册Heroku Postgres服务很容易。 只需在浏览器中转到postgres.heroku.com ，然后单击页面中间的立即注册按钮即可。 如果您已经有一个Heroku帐户，则只需提供您已使用的电子邮件地址和密码即可登录。否则，您可以立即创建一个新帐户。
Of course, you’ll need to provide your credit card details to pay for the Heroku Postgres service. If you’ve just created a new account, you’ll be taken to the Verify Account page where you can provide the necessary details.
当然，您需要提供您的信用卡详细信息才能使用Heroku Postgres服务。 如果您刚刚创建了一个新帐户，则将转到“验证帐户”页面，您可以在其中提供必要的详细信息。
Once you’ve logged in and verified, and if you’re not looking at it already, go to the Your Databases page accessible from the Your DBs entry in the top menu. This page is the dashboard from which you can monitor and manage all of your database instances.
登录并验证后，如果尚未查看，请转至可从顶部菜单中的“数据库”条目访问的“数据库”页面。 此页面是仪表板，您可以从中监控和管理所有数据库实例。
Click the + pointed to by the arrow to add a database; you’ll be presented with the list of plans from which you can pick. Select your desired plan and then the Add Database button to confirm the selection.
单击箭头指向的+，以添加数据库； 系统会为您提供可供选择的计划列表。 选择所需的计划，然后选择“添加数据库”按钮以确认选择。
In just a few minutes you’ll have a nice, new, shiny provisioned database instance ready for your data. When it’s ready, click on it to drill down and see its connection information.
在短短的几分钟内，您将拥有一个漂亮的，新的，闪亮的预配置数据库实例，可以为您的数据做好准备。 准备就绪后，单击它以向下钻取并查看其连接信息。
Now that you have a cloud-hosted PostgreSQL database running, let’s try interacting with it from PHP.
现在您已经运行了一个云托管的PostgreSQL数据库，让我们尝试从PHP与之交互。
Connect to the database using PostgreSQL’s interactive terminal client. If you are running PostgreSQL locally then you will already have the client installed; otherwise you’ll have to turn to your platform’s package management suite (e.g. on Ubuntu run sudo apt-get install postgresql-client) or go to postgresql.org, where you’ll find available for download pre-built binary packages suitable for FreeBSD, Linux, Mac, Solaris and Windows, as well as other installation options.
使用PostgreSQL交互式终端客户端连接到数据库。 如果您在本地运行PostgreSQL，那么您将已经安装了客户端。 否则，您将不得不转到平台的软件包管理套件(例如，在Ubuntu上运行sudo apt-get install postgresql-client )或转到postgresql.org ，在这里您可以找到适用于FreeBSD的预编译二进制软件包。 ，Linux，Mac，Solaris和Windows以及其他安装选项。
To connect, call psql with the -h option to specify the server’s hostname, -U to specify the username, and then the database name:
要进行连接，请使用-h选项调用psql来指定服务器的主机名，使用-U来指定用户名，然后是数据库名：
timothy@zarkov:~$ psql -h ec2-184-73-194-179.compute-1.amazonaws.com -U dj1wcxb3x9fy3x5 ul28zxpr39no1rr Password for user ul28zxpr39no1rr: ******** psql (8.4.9, server 9.0.5) WARNING: psql version 8.4, server version 9.0. Some psql features might not work. SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. dj1wcxb3x9fy3x5=>Create a table to store some employee information using the following SQL:
使用以下SQL创建一个表来存储一些员工信息：
CREATE TABLE employees ( employee_id SERIAL, last_name VARCHAR(30), first_name VARCHAR(30), title VARCHAR(50) );employee_id is a SERIAL field and will be the employee table’s primary key. last_name, first_name, and title are VARCHAR fields which will store the individual’s name and job title.
employee_id是SERIAL字段，将成为employee表的主键。 last_name ， first_name和title是VARCHAR字段，将存储个人的姓名和职务。
Then, insert some records into the table so there will be some data to work with:
然后，将一些记录插入表中，以便可以使用一些数据：
INSERT INTO employees (last_name, first_name, title) VALUES ('Abreu', 'Mark', 'Project Coordinator'), ('Nyman', 'Larry', 'Security Engineer'), ('Simmons', 'Iris', 'Software Architect'), ('Miller', 'Anthony', 'Marketing Manager'), ('Leigh', 'Stephen', 'UI Developer'), ('Lee', 'Sonia', 'Business Analyst');As with the terminal client, most developers will probably be running a version of PHP installed using package management tools. If that’s the case, the PostgreSQL extensions can be enabled in PHP by installing PostgreSQL support (e.g. sudo apt-get install php5-pgsql).
与终端客户端一样，大多数开发人员可能正在运行使用软件包管理工具安装PHP版本。 如果是这样，可以通过安装PostgreSQL支持在PHP中启用PostgreSQL扩展(例如sudo apt-get install php5-pgsql )。
There are actually two possible PHP extensions you can use to work with PostgreSQL – the PostgreSQL extension which is essentially a wrapper for the C client library libpq, and PDO using the PDO_PGSQL driver. Both are generally installed together, but here I’ll be using PDO since it offers a consistent API to work with a variety of databases and is generally regarded as the appropriate interface for working with databases in modern, professional code.
实际上，可以使用两种可能PHP扩展与PostgreSQL一起使用-PostgreSQL 扩展本质上是C客户端库libpq的包装，以及使用PDO_PGSQL驱动程序的PDO 。 两者通常都安装在一起，但是在这里我将使用PDO，因为它提供了与各种数据库一起使用的一致API，并且通常被认为是在现代专业代码中使用数据库的合适接口。
The connection is established by creating a new PDO object, passing in a DSN (Data Source Name) string at the time of instantiation. The string contains the information displayed in the Connection Settings section for the database.
通过创建新的PDO对象来建立连接，该对象在实例化时传入DSN(数据源名称)字符串。 该字符串包含数据库的“连接设置”部分中显示的信息。
<?php $dsn = "pgsql:" . "host=ec2-184-73-194-179.compute-1.amazonaws.com;" . "dbname=ul28zxpr39no1rr;" . "user=dj1wcxb3x9fy3x5;" . "port=5432;" . "sslmode=require;" . "password=p28xwd9pjcrzyzp6mf74m99cze"; $db = new PDO($dsn);The DSN starts with “pgsql” to specify use of the PDO_PGSQL driver, and then each piece of information is presented as a key=value pair separated from one another by semicolons.
DSN以“ pgsql”开头以指定PDO_PGSQL驱动程序的使用，然后每条信息都以键=值对的形式出现，以分号分隔。
Now, with a connection established to the database, you can send queries in the same manner as if you had connected to a local-running instance.
现在，在建立与数据库的连接之后，您可以按照与连接到本地运行实例相同的方式发送查询。
<html> <head> <title>Employees</title> </head> <body> <table> <thead> <tr> <th>Employee ID</th> <th>Last Name</th> <th>First Name</th> <th>Title</th> </tr> </thead> <tbody> <?php $query = "SELECT employee_id, last_name, first_name, title " . "FROM employees ORDER BY last_name ASC, first_name ASC"; $result = $db->query($query); while ($row = $result->fetch(PDO::FETCH_ASSOC)) { echo "<tr>"; echo "<td>" . $row["employee_id"] . "</td>"; echo "<td>" . htmlspecialchars($row["last_name"]) . "</td>"; echo "<td>" . htmlspecialchars($row["first_name"]) . "</td>"; echo "<td>" . htmlspecialchars($row["title"]) . "</td>"; echo "</tr>"; } $result->closeCursor(); ?> </tbody> </table> </body> </html>Here the code assumes the instantiation of $db has taken place and submits a SELECT query to retrieve the employee information from the remote PostgreSQL database – the employee id, last name, first name, and job title.
这里的代码假定$db的实例化已经发生，并提交SELECT查询以从远程PostgreSQL数据库中检索员工信息-员工ID，姓，名和职务。
Bookended by the appropriate HTML markup to render a table, the code iterates through the retrieved records and outputs each field. Finally, the results cursor is closed to free the connection to the server.
通过适当HTML标记预订以呈现表，该代码遍历检索到的记录并输出每个字段。 最后，关闭结果游标以释放与服务器的连接。
The output of the script resembles the following:
脚本的输出类似于以下内容：
More and more developers are realizing that it makes sense to outsource their database hosting to cloud-service provides like Heroku. This lets you focus more on writing your application’s code and a handful of SQL queries rather than learning how to be a DBA.
越来越多的开发人员意识到，将他们的数据库托管外包给像Heroku这样的云服务提供商是有意义的。 这使您可以将更多的精力放在编写应用程序的代码和少数SQL查询上，而不是学习如何成为一名DBA。
In this article, you saw how easy it is to sign up and get started using Heroku Postgres. The whole process literally takes minutes from start to finish, and you can connect to the database from PHP using PDO like any other PostgreSQL database.
在本文中，您看到了注册和开始使用Heroku Postgres的过程是多么容易。 从头到尾，整个过程实际上需要几分钟，您可以像其他任何PostgreSQL数据库一样使用PDO从PHP连接到数据库。
If your web application uses PostgreSQL, then you definitely should investigate Heroku’s offerings. You may find they’re exactly right for you.
如果您的Web应用程序使用PostgreSQL，那么您绝对应该研究Heroku的产品。 您可能会发现它们完全适合您。
Image via Jozsef Bagota / Shutterstock
图片来自Jozsef Bagota / Shutterstock
翻译自: https://www.sitepoint.com/heroku-postgresql/
postgresql ::
相关资源：go_api_boilerplate：:dog_face:Go（Golang）:rocket:RESTGraphQL API + Postgres样板-源码