web
render
postgre_sql

2025-01-31

What I wanted to do

I wanted to migrate my existing free tire Render Postgres to a new Render Postgres in the same project.

I thought it might not be possible to access, backup, migrate, or export the database because i am using free tier and i saw that Render free tier postgres does not support backup. but it was possible.

why?

  • the website will be mostly used in Korea but the database and the server’s region were Oregon, because when initialy deploying i just followed the tutorial without thinking.
  • I’ve always thought the loading speed of the Strapi Admin panel was too slow. and the long distance between Oregon and Korea might be the reason.

How?

1. create new Render Postgres

  • I created a new Render Postgres in Singapore.
  • There was no closer region to Korea in Render.
  • I also couldnt change the hosting service because i couldnt find cheaper and easier Strapi deployment + PostgreSQL database option than Render’s.

2. export .sql data from the old database

PGPASSWORD={old_password} pg_dump -h {old_host_name} -U {old_user_name} {old_database_name} -n public --no-owner > database_dump.sql`

and i got this error

error: could not translate host name " " to address: nodename nor servname provided, or not known.

→ it’s not the ‘Hostname’ in Renderpostgres setting Info/Connections. See External Database URL and find something like oregon-postgres.render.com.

and i got this error

pg_dump: error: server version: 16.6 (Debian 16.6-1.pgdg120+1); pg_dump version: 14.15 (Homebrew)

pg_dump: error: aborting because of server version mismatch

→ I installed postgresql 16

brew install postgresql@16

but got same error. → I installed? pg_dump

export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"
source ~/.zshrc
pg_dump --version    // pg_dump (PostgreSQL) 16.6 (Homebrew)

and i tried again, it worked. i found database_dump.sql created in my Finder.

3. import data to the new database

PGPASSWORD={new_password} psql -h {new_host_name} -U {new_user_name} -d {new_database_name} < database_dump.sql

4. Edit the environment variables

  • I put environment variables of the new database into the .env file
  • also into the new Strapi web service deployed on Render
  • re-deployed Strapi web service

I opened new Strapi admin panel, had 502 bad gateway temporarily but it worked. i knew it because the web worked, and the new database logs were updated while the old database logs were no longer updated.

oregon에서 singapore로 옮긴 결과는?

disable cache하고 새로고침해서 비교해봤을때 새로운 웹사이트가 1-3s 정도 적게 찍히는 것을 보아 빨라지긴 한 것 같다. 체감상으로도 더 부드럽게 느껴진다. 빨라진거 맞겠지?

Project: kmuvcd