confrnz - Tumblr blog

confrnz · 7 years ago

Text

Debugging Postgresql on Your Local Machine

Let’s look at some of the ways that we can interact with Postgresql and figure out how to make it work with our Rails app locally.

First off, for ease of working, you can open up an additional tab on your shell while the shell simultaneously keeps hosting the RoR environment. Once you open up a new tab and navigate to the program directory so you can work, remember the best practice of shortening your bash shell path, the same way that we did in the previous post:

$ export PS1='\W$ '

Once you have that going, let’s see if we can connect to the postgres server:

confrnz_app$ psql

As a result we get:

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

So in this situation, evidently we can’t connect to postgres. Something we forgot to do in the last post was to ensure that all of our gemfiles were updated. Typically before you host a rails app, you want to do the following:

$ bundle install

What that does is makes sure all dependencies in your Gemfile are available to your application. This is something called bundler. So going back to ensure that bundler is installed, you will want to run:

$ gem install bundler

Once you have done this, you can run “rails s” once again, but you will still come out with the same error.

Let’s update postgres with:

$ brew install postgres

After installation, we see:

postgresql 10.5 is already installed and up-to-date To reinstall 10.5, run `brew reinstall postgresql`

So now that we are running an updated version of postgres. To make sure we have all of our, “foundations” correct and software installed correctly, let’s go back and make sure everything is installed and up-to date on our local machine. This is a manual process, but is a good way to make sure all of the foundations of the, “versions” of various programs are working how we would expect them to do so. In the future, we can avoid much of this manual work by setting up something called, “docker” which is essentially a program which runs on local machines that allows an “image” to be run that mimics the runtime environment identical to what you would have in production. For example, we can load the Heroku-16 stack right into docker, install all of the programs that we would like to have ready to go (such as postgres) and test the, “production environment,” right on our local computers (in theory). That’s a lot of work and for now we’re just trying to get things up and running, so let’s keep going through the manual process for now.

Let’s make sure rails is installed.

$ gem install rails

Let’s install the postgres gem.

$ gem install pg

We may go back and choose to re-install other gems along the way if we see specific gems being called out within errors to ensure that we are completely up-to-date, but let’s leave this aside for now.

Rake in Ruby on Rails

What does it mean in Ruby to, “rake” something?

In Rails, Rake is used for common administration tasks, especially sophisticated ones that build off of each other. It’s basically your admin/devops utility kit for Ruby on Rails.

There are a variety of tools you can use within rake, that are linked to here. Something important to keep in mind is that since this is an “in-app” tool, we have to feed all of our environmental variables that is being required on the front-end if we haven’t hard-coded it into the RoR app, so in our case we have to add, SPARKPOST_API_KEY=123 (assuming that the errors ask for it - in the future we may add this into the development environment to create more convenience).

So for example we can run:

$ SPARKPOST_API_KEY=123 rake about

To get information about the app itself. Go ahead, try it and see what comes up!

There are additional rake top line commands which can be used, summarized below:

rake db

The most common tasks of the db: Rake namespace are migrate and create, and it will pay off to try out all of the migration rake tasks (up, down, redo, reset). rake db:version is useful when troubleshooting, telling you the current version of the database.

rake doc

can be used to generated documentation within the doc/app file, as well as on guidelines, APIs and plugins rake notes will search through your code for comments beginning with FIXME, OPTIMIZE or TODO. The search is done in files with extension .builder, .rb, .erb, .haml and .slim for both default and custom annotations.

rake routes

will list all of your defined routes, which is useful for tracking down routing problems in your app, or giving you a good overview of the URLs in an app you’re trying to get familiar with.

rake test

A good description of unit testing in Rails is given in A Guide to Testing Rails Applications

rake tmp

The Rails.root/tmp directory is, like the *nix /tmp directory, the holding place for temporary files like sessions (if you’re using a file store for files), process id files, and cached actions.

rake stats

is great for looking at statistics on your code, displaying things like KLOCs (thousands of lines of code) and your code to test ratio.

rake secret

will give you a pseudo-random key to use for your session secret.

rake time:zones:all

lists all the timezones Rails knows about.

So how do we use the rake db: database tool?

More information about “rake db” commands can be found in the database migrations guide.

db:migrate

runs (single) migrations that have not run yet.

db:create

creates the database

db:drop

deletes the database

db:schema:load

creates tables and columns within the existing database

db:setup

runs db:create, db:schema:load, db:seed

So at this point, let’s start out and assume we don’t have a database running, or that we should take a database and kill it, and restart it to see if that works. We can do this with the following:

$ SPARKPOST_API_KEY=123 rake db:drop

$ SPARKPOST_API_KEY=123 rake db:create

We see that no matter what we do, whether we reset the database, drop and create it, we keep getting the same original error:

PG::ConnectionBad: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

How about we go and check whether postgres is even running in the first place?

$ ps auxwww | grep postgres

Basically what this is doing is on MacOS, “ps aux” shows all of the processes running on your machine, with the command “ps” being modified by “aux” to show which types of processes. The | (pipe) command means send information from one program to another for processing, so basically send from “ps” to “grep” and “grep” means “search for a string of characters,” so it is essentially a filter for, in this case, “postgres” The “wwww” at the end of aux controls the sizing as displayed within the terminal.

From this, we get a bunch of information about different postgres processes running. We don’t know which one is which at this point.

Side Note - Difference Between psql and postgres

Side note, taking a minute to understand the difference between “postgres” and “psql” commands within the context of the terminal.

postgres is the server itself, and runs separately from all of the client connections. It is almost never run by hand. Even if you want to start and stop it, this is normally done through pg_ctl or an init script if you installed a postgres package from your distribution.

psql is the command-line client, that connects to the server and allows you to execute individual queries by hand.

If we run the same command above and look at psql, which is our command-line client that connects us to the server, we see a different output:

So at this point, we should check whether we can even use the command-line client, “psql” to log into the server. This would be a good test - if we can’t log in, then probably RoR can’t log in with its gem either.

$ psql

We see that using this command, we get the same error. So there appears to be some kind of problem with whether the server is listening at all.

By default, postgres listens on port 5432. We can filter for all connections lisetning on this port by using the following command:

netstat -an | grep 5432

netstat -an ("network statistics") is a command-line tool that displays network connections (both incoming and outgoing), routing tables, and a number of network interface (network interface controller or software-defined network interface) and network protocol statistics. The -an just shows everything on the computer in question, and grep 5432 filters out for that port.

If that command produces no output, then postgres is not listening on the normal port, or is not listening on any port.

Is The Server Running?

The following command will output directly whether the server is running or not.

$ pg_ctl -D /usr/local/var/postgres status

Starting The Server

$ postgres -D /usr/local/var/postgres

Other Notes

WARNING ABOUT postmaster.pid File

PostgreSQL puts a file named postmaster.pid in the data directory to store the process id of the PostgreSQL server process. If PostgreSQL crashes, this file can contain an old pid that confuses PostgreSQL.

The Postgres help file states:

If you delete the postmaster.pid file while PostgreSQL is running, bad things will happen.

If you’re having problems, check there is no postmaster.pid in your postgres directory, probably /usr/local/var/postgres/

Postgres App

The postgres app itself can be found here. You have to physically run postgres in order to be able to have a port to listen to. Postgres has to be running and “open” order for the app to function in the first place.

Postgres.app includes psql, a versatile command line client for PostgreSQL. But it’s not the only option; there are plenty of great graphical clients available for PostgreSQL. Two popular tools are Postico and PGAdmin4.

Once you download the PostgreSQL app, it should look like the following upon initialization (prior to this, you have to hit “start” in order to get the program running):

Now that we are running postgres, we should be able to connect to it via various programs. Within shell, we now do the command:

$ psql

And for output we get:

psql (10.5)

Now we’re getting somewhere. If we use the database management tool, “Postico” we see the following upon connecting to “localhost”

Now that we are fairly confident that postgres is actually running, let’s go back and see if we can create a database using the RoR rake tool.

$ confrnz_app$ SPARKPOST_API_KEY=123 rake db:create Created database 'project_development' Created database 'project_test'

OK, that sounds good. Now we seem to have a ‘project_development’ database. What happens if we attempt to serve the site?

ActiveRecord::PendingMigrationError - Migrations are pending. To resolve this issue, run:

bin/rails db:migrate RAILS_ENV=development

Ah yes, that’s right - we need to migrate the database after creating it. What does it mean to migrate a database? Basically it just means to create the table within the structure that we are looking to create.

$ SPARKPOST_API_KEY=123 rake db:migrate

After this command is run, you will see a bunch of commands on the terminal being run in which the tables are being created. After this, run:

$ SPARKPOST_API_KEY=123 rails s

Success! Let’s review. After we have ensured that the following is complete, everything works.

1. Database is running by downloading, installing and running Postgres.

2. We re-installed things as needed to prevent versioning errors for all of the programs we have installed on our machine.

3. We created a data base, then migrated it.

4. We served the program in development mode, injecting environmental variables to make sure that it runs.

Other Problems

Old postmaster.pid File Message

One potential problem upon attempting to run Postgres within the PGAdmin4 GUI, you could run into is the following message:

You may also see an error as follows in the command terminal:

2018-10-14 19:32:06.620 CDT [41200] DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.5.

Many Instances of Postgres

This answer on StackExchange/SuperUser mentions the following:

Public service announcement: never delete postmaster.pid. Really. Great way to get data corruption.

Within this scenario, you may get the following message:

No Such postmaster.pid File or Directory

Running command as follows, we get the following error:

$ cat: /usr/local/var/postgres/postmaster.pid: No such file or directory

In the Database Administrator StackExchange, points to another StackOverflow answer about Postgres in general.

You can restart the server with the following command:

$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

However if you try to manually restart it, you might get the following response:

So instead you can try the following to stop and then restart postgresql (brew servies):

$ brew services stop postgresql

$ brew services start postgresql

After restarting the server, you might be able to get things going. However if this does not work, the best way to get everything going might be within the Postgres troubleshooting file, “Resetting the Postgres App.”

To do this, perform the following:

Quit Postgres.app

Open Activity Monitor, see if any processes name postgres are running. If so, kill them. Kill the process with the lowest pid first; child processes are respawned automatically after killing them.

Delete the Folder ~/Library/Application Support/Postgres

Delete all settings using the command: defaults delete com.postgresapp.Postgres2

Open Postgres.app again

If you can’t follow this above, try uninstalling and reinstalling Postgres:

$ brew uninstall postgresql

$ brew install postgres

Different Version Usage of Postgresql

There are several versions of Postgresql, which can be viewed here:

https://formulae.brew.sh/formula/postgresql

If you want to install an exact version of postgres, for example postgresql10, use:

$ brew reinstall postgresql@10

Then, update the database from a previous version to the current version.

$ brew postgresql-upgrade-database

Once you have reinstalled and upgraded the database to migrate to version 10...

$ pg_ctl -D /usr/local/var/postgres status

Should output the following:

Port 5432 “Already In Use”

Postgres when installed by brew uses a launch daemon. The launch deamon controls the Postgres process and it automatically restarts Postgres after it is killed. To find the daemon, try:

$ sudo launchctl list | fgrep postg

Then you will get something like:

Which is the name of the daemon.

Then use:

$ sudo launchctl stop <name>

...and replace <name> with the output from the command above.

Kill All Postgres Servers On Your Mac

Using Brew Services

An article about using Brew services to control postgresql can be found here.

#postgres #postgresql

0 notes