Postgres error "invalid value for" TimeZone ":" UTC ""

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 while trying to go to my postgres database. help would be much appreciated!

Answer 1

I had the same problem using Postgres.app from Heroku. Rebooting my Mac solved it.

Answer 2

Restarting postgresql is working.

To reboot if you installed it using homebrew, brew info postgresql will tell you:

 launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist 

Answer 3

brew services restart postgresql

Answer 4

Try restarting the server. I updated Postgresql via Homebrew, but forgot to restart the server and got the same problem. I believe this is due to the fact that the client and server versions do not match. psql started with:

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

Answer 5

I don't think I deserve any points for this, but rebooting my Postgres.app (which is better than rebooting the whole system) solved it for me. The application does not appear on the Dock, you can find it in the navigation bar at the top of the window. Hope it helps anyway.

Answer 6

I also had this problem.

Log in to the database, then do:

 set time zone utc; 

Answer 7

If nothing is fixed and you are using homebrew , chances are you have problems with current links.

Assuming you have two versions of Postgres installed, make sure you disconnect and then reconnect. In my case, I need two versions working 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 

It made me work at the same time. Hope this gets helpful as it took me a while to figure it out!

Answer 8

In fact, it happened that you updated the postgresql server and cleared your old folders, but you did not restart your postgresql server. Server searched for timezone files in a remote directory

Answer 9

just reloading the database helped. Homebrew updated my Postgres installation and I have not restarted it yet.

Answer 10

In my case, rebooting the database did not help. Updating tzdata ( apt-get install tzdata ) did the trick for me.

Answer 11

Just a short link for those who do not use Postgres.app, but run psql from the command line or via launchctl. You will need to configure the following, where you have the 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 

Answer 12

I had a similar problem after updating the timezone information, i.e. loading the IANA database and compiling using zic.

My problem actually started after restarting PostgreSQL. I received an invalid value for parameter TimeZone: UTC and restarting again did not help to solve the problem.

It turns out that the information about my time zone was completely corrupted after the update. I had dangling symbolic links in /usr/share/zoneinfo . From the psql console, I got:

 mydb=# SELECT * FROM pg_timezone_names; ERROR: could not stat "/usr/share/zoneinfo/PRC": No such file or directory 

I removed all such dangling symbolic links. After that, 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 symbolic link:

 cd /usr/share/zoneinfo ln -s Etc/UTC UTC 

After that, SET time zone 'UTC' worked correctly.

Answer 13

Obviously, this also happens with Java / JDBC when connecting to Postgres.

The solution is to tell JDBC about the correct user timezone in Postgres when the connection is received.

So, explicit mentioning of the user time zone when starting the program helps:

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

Remarks:

Adding here because it is the first result on Google for this problem with connecting to Postgres!

Source:

This comment by Yuri 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 14

Based on @MathiasJ's answer, instead of rebooting the whole machine, I ran

 brew services restart postgresql@9.6 

and my subsequent rake db:create worked fine.