### Author:  [@watchmanmonitor](https://meta.discourse.org/u/watchmanmonitor)
#### Post date: [August 25, 2016, 4:35pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/1 "2016-08-25T16:35:27Z")
Continuing the discussion from [Discourse Docker Image version 1.3.3, released](https://meta.discourse.org/t/discourse-docker-image-version-1-3-3-released/44778):
> [@sam](#):
>
> Trouble upgrading PostgreSQL
>
> On some very old images of Discourse the automatic pg upgrade may fail. If this happens the upgrader will display the commands you need to run to get up and running. In a nutshell you need to “backup” your db to a folder and then restore it from the folder into the new version. Alternatively, you can amend your template to run against pg 9.3
So, I have been using postgres 9.3 since the new docker image came out.. and just replacing `templates/postgres.9.3.template.yml` with `templates/postgres.template.yml` doesn’t work.
Given a current backup, what’s a good way to upgrade the postgres template?
---
### Author:  [@mpalmer](https://meta.discourse.org/u/mpalmer)
#### Post date: [August 25, 2016, 10:44pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/2 "2016-08-25T22:44:59Z")
Changing the template _should_ work; it’s how we did all the 9.3-\>9.5 migrations here at CDCK. Can you expand on “doesn’t work”?
---
### Author:  [@watchmanmonitor](https://meta.discourse.org/u/watchmanmonitor)
#### Post date: [August 26, 2016, 3:25pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/3 "2016-08-26T15:25:28Z")
Here’s the output I get when I `./launcher rebuild app` after removing .9.3 from my postgres template
>
I remember having issues upgrading to the 1.3.3 image, perhaps because I didn’t know to remove .9.3 then?
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [August 26, 2016, 3:28pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/4 "2016-08-26T15:28:48Z")
There were a couple older sites on Digital Ocean that failed to upgrade to Postgres 9.5 and I stuck with the old template for 9.3. As I recall it had to do with default db encodings being incorrect on the older images.
---
### Author:  [@watchmanmonitor](https://meta.discourse.org/u/watchmanmonitor)
#### Post date: [August 27, 2016, 8:38pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/5 "2016-08-27T20:38:55Z")
Lately I’ve had a few stalls while using the web-based upgrade on this one discourse of mine. Manual rebuilds ( `./launcher rebuild app` ) worked, but that’s downtime I’d rather avoid.
There was mention of postgres in the logs I saw… I’ll save them if they happen again.. and I saw that I was still on old postres 9.3
Is that a problem to address?
---
### Author:  [@mpalmer](https://meta.discourse.org/u/mpalmer)
#### Post date: [August 28, 2016, 12:30am UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/6 "2016-08-28T00:30:48Z")
100 UIP to @codinghorror. From the gist:
```
encodings for database "postgres" do not match: old "SQL_ASCII", new "UTF8"
```
The smoothest option for upgrading is almost certainly going to be just to run a full backup of the existing site, spin up a new site, and restore into it, then switch over. If you’ve got access to someone who is a PostgreSQL ninja, there are ways of doing encoding rebuilds (with DB dumps and some judicious editing), but it’s not for the faint of heart, and I, for one, am not going to load the foot-gun by trying to write a sufficiently detailed howto…
---
### Author:  [@watchmanmonitor](https://meta.discourse.org/u/watchmanmonitor)
#### Post date: [August 28, 2016, 1:16am UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/7 "2016-08-28T01:16:47Z")
Torching this container and spinning up a new one seems great.
For the docker novice that I am, is there already a how-to for starting the container over on the same host?
---
### Author:  [@mpalmer](https://meta.discourse.org/u/mpalmer)
#### Post date: [August 28, 2016, 1:42am UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/8 "2016-08-28T01:42:57Z")
Running a second container is straightforward, in general: copy `containers/app.yml` (or whatever you’ve called it) into, say, `containers/app2.yml`, edit that new file a bit, and run `./launcher bootstrap app2; ./launcher start app2`. The tricky bit is getting HTTP into the new container; if you’re doing the default setup, where the HTTP port is being forwarded directly into the container, you’ll need to use a different port for the new container, and a bit of the ol’ quick-rebuild-shuffle when you go to make the new site live. If you’re using nginx/haproxy/apache to proxy multiple websites, it’s a bit easier, because you can just setup the new site on a new name and add it to the proxy config.
---
### Author:  [@watchmanmonitor](https://meta.discourse.org/u/watchmanmonitor)
#### Post date: [August 28, 2016, 6:49pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/9 "2016-08-28T18:49:41Z")
How can I just start over?
I think what I want to do here is this:
1- download my backup
2- remove the current container (yes, our site will be down for a bit)
3- remove any backups so that a new container won’t try to use them
4- start a fresh container in place
5- re-import this backup
- I don’t know how to do step 2 (remove the container)
- I don’ t know if step 3 is relevant, or is a part of step 2
- I guess step 4 is `./launcher bootstrap app`
---
### Author:  [@mpalmer](https://meta.discourse.org/u/mpalmer)
#### Post date: [August 28, 2016, 9:59pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/10 "2016-08-28T21:59:31Z")
If you’re OK with some downtime, then this should work:
1. Take and download backup
2. `./launcher stop app`
3. `docker rm app`
4. `mv shared/standalone shared/standalone.9.3`
5. Edit `containers/app.yml` to change the `postgres.9.3` to `postgres`
6. `./launcher bootstrap app; ./launcher start app`
7. Import the backup
Step 4 is the step that “makes the current container cease to exist” in a meaningful sense. `shared/app` is where all of the PostgreSQL data, logs, etc live. By renaming it, rather than just deleting it, you’re leaving yourself with a fallback plan if the unexepected happens and everything goes to hell.
---
### Author:  [@watchmanmonitor](https://meta.discourse.org/u/watchmanmonitor)
#### Post date: [August 28, 2016, 11:44pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/11 "2016-08-28T23:44:12Z")
Thanks!
I updated everything & got a fresh backup, then started at:
```plaintext
mv shared/app shared/app.9.3`
mv: cannot stat ‘shared/app’: No such file or directory
```
I knew there was something wrong because it wouldn’t tab complete `app`
So I looked in shared to see what’s up:
```plaintext
# ls -al shared/
total 12
drwxr-xr-x 3 root root 4096 May 24 2014 .
drwxr-xr-x 11 root root 4096 Aug 22 11:47 ..
-rw-r--r-- 1 root root 0 May 24 2014 .gitkeep
drwxr-xr-x 13 root root 4096 Aug 25 11:05 standalone
```
I assume that `standalone` was correct some time ago, and I missed a memo about moving that.
Perhaps I need to move `standalone` to `app` so that the normal postgres template knows where to look?
---
### Author:  [@mpalmer](https://meta.discourse.org/u/mpalmer)
#### Post date: [August 28, 2016, 11:47pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/12 "2016-08-28T23:47:40Z")
No, it doesn’t need to be moved; I had the wrong name in shared. It should be `s/app/standalone/` in step 4, and I’ll edit my post to reflect that.
---
### Author:  [@watchmanmonitor](https://meta.discourse.org/u/watchmanmonitor)
#### Post date: [August 29, 2016, 12:52am UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/13 "2016-08-29T00:52:27Z")
I had to move the ssl certificate components over to the new `/var/discourse/shared/standalone`, but otherwise this process worked well. 💯
It visually got stuck at
`[2016-08-29 00:19:39] Extracting uploads... `
but when I opened a new window to log in, the system message of successful restore greeted me nicely.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [June 8, 2024, 12:45pm UTC](https://meta.discourse.org/t/trouble-upgrading-postgresql-from-9-3-to-current/49207/14 "2024-06-08T12:45:19Z")
This topic was automatically closed after 2843 days. New replies are no longer allowed.