Postgres error “invalid value for parameter ”TimeZ

Question

Jupitor$ bundle exec rake db:create db:migrate
APP_development already exists
rake aborted!
PG::Error: ERROR:  invalid value for parameter "TimeZone": "UTC"
: SET time zone 'UTC'

I keep getting this error when trying to migrate to my postgres database. help would be much appreciated!

Answer 1

If nothing else fixes and you happen to be using homebrew, chances are you have issues with current links.

Assuming you have two Postgres versions installed, make sure you unlink and then link again. In my case, I needed the two versions working in order to run pg_upgrade. I have postgresql95 and postgresql so I did:

$ brew unlink postgresql
$ brew unlink postgresql95
$ brew link postgresql95
$ brew link --overwrite postgresql 

That got me both working at same time. Hope it becomes helpful as it took me a good while to figure that out!

Answer 2

brew services restart postgresql did not fix for me. I'm sure rebooting would've worked, but I wanted to figure out the cause of the issue.

---

I believe the issue was caused for me because of two conflicting versions of postgresql.

I already had postgresql running with brew services, and then installed postgresql@11 which left postgresql running in brew services even after I uninstalled postgresql.

I fixed this by stopping the postgresql brew service, even though it wasn't listed in brew services list.

---

Steps to reproduce:

$ brew install postgresql
$ brew services start postgresql

$ brew install postgresql@11
$ brew uninstall postgresql

$ brew services start postgresql@11

How to fix:

$ brew services stop postgresql

Opened an issue on Homebrew requesting that a formula's service should automatically be stopped upon uninstall.

Answer 3

Apparently, a similar thing also happens with Java/JDBC while connecting to Postgres.

The solution there is to tell JDBC to report the correct user timezone to Postgres while getting the connection.

So, explicitly mention the user timezone while starting the program helps:

java -Duser.timezone=America/Los_Angeles com.example.MyMainClass

Note:

Adding this here because this happens to be the first result on Google for this issue with connecting to Postgres!

Source:

This comment by Yuriy on the Jira support forum: https://community.atlassian.com/t5/Jira-questions/invalid-value-for-parameter-quot-TimeZone-quot-quot-US-Pacific/qaq-p/839426

Answer 4

What actually happened is that you upgraded the postgresql server and cleaned-up your old folders but you haven't restarted your postgresql server. The server searched for the timezones files in the deleted dir

Answer 5

brew services restart postgresql

Answer 6

Try restarting the server. I updated Postgresql through Homebrew but forgot to restart the server and got this same problem. I believe it's due to the client and server versions not matching. psql started with:

$ psql
psql (9.1.4, server 9.1.2)
Type "help" for help.