Postgres error "invalid value for parameter "TimeZone": "UTC""
Ruby on-Rails-3PostgresqlRuby on-Rails-3 Problem Overview
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!
Ruby on-Rails-3 Solutions
Solution 1 - Ruby on-Rails-3
I had the same problem using the Postgres.app from Heroku. Rebooting my Mac solved it.
Solution 2 - Ruby on-Rails-3
Restarting postgresql works.
To restart if you've installed it using homebrew, brew info postgresql
will tell you to:
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Solution 3 - Ruby on-Rails-3
brew services restart postgresql
Solution 4 - Ruby on-Rails-3
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.
Solution 5 - Ruby on-Rails-3
Based on @MathiasJ's answer, instead of rebooting my entire machine, I ran
brew services restart postgresql@9.6
and my subsequent rake db:create
worked perfectly.
Solution 6 - Ruby on-Rails-3
I don't think I deserve any points for that but rebooting my Postgres.app (which is better than rebooting the whole system) solved it for me. The app doesn't show up on the Dock, you can find it on the navbar at the top of your window. Hope it helps anyway.
Solution 7 - Ruby on-Rails-3
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!
Solution 8 - Ruby on-Rails-3
I also had this problem.
Login to the database then issue:
set time zone utc;
Solution 9 - Ruby on-Rails-3
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
Solution 10 - Ruby on-Rails-3
In my case restarting the database didn't help. Updating tzdata (apt-get install tzdata
) did the trick for me.
Solution 11 - Ruby on-Rails-3
just restarting the database helped. Homebrew updated my Postgres installation and I did not restart yet.
Solution 12 - Ruby on-Rails-3
I had a similar problem after updating time zone information, that is, downloading the IANA database and compiling using zic.
My problem actually began after restarting PostgreSQL. I got invalid value for parameter TimeZone: UTC
and restarting again did nothing to solve the problem.
It turns out my time zone information was completely messed up after the update. I had dangling symbolic links in /usr/share/zoneinfo
. From a psql console, I got:
mydb=# SELECT * FROM pg_timezone_names;
ERROR: could not stat "/usr/share/zoneinfo/PRC": No such file or directory
I deleted all such dangling symlinks. After doing this, at least I could get SELECT * FROM pg_timezone_names
to work, but still got the same invalid value...
error.
What finally solved the problem for me was creating a new symlink:
cd /usr/share/zoneinfo
ln -s Etc/UTC UTC
After this, SET time zone 'UTC'
worked correctly.
Solution 13 - Ruby on-Rails-3
Just a quick reference for those that are not using Postgres.app, but that start psql from the command line or through launchctl. You'll need to adjust the following for where you have your Postgres data and log files located at:
pg_ctl stop
pg_ctl start -D /usr/local/pgsql/data/ -l /usr/local/pgsql/log/server.log
Solution 14 - Ruby on-Rails-3
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.
Solution 15 - Ruby on-Rails-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