Problem configuring local PostgreSQL server

Get Help and Help Others
1

Introduction

Trying to create my own minimalistic RW App, I took the Example Repo, and in order to make it also easy to deploy I followed the section Local Postgres Setup VERY CAREFULLY.

Well, it seems that @rob was right in stating:

Install Postgres? I’ve messed up my Postgres installation so many times, I wish I could just uninstall everything and start over!

I felt the same way a few years back when reading Local Postgres Setup for the first time. Not being lazy and being interested in helping others in a possibly similar situation, I created and published the article How to install PostgreSQL with EDB Installer.

Problem description

I will immediately admit my weakness in this general area (databases), even if this time I run into troubles not with installation, but rather configuration. Here is my problem presented in the form of a screenshot of PgAdmin 4

image
image1858×1170 255 KB

My database is rw-auth, and the (default) owner is postgres. Assuming that I set everything right (.env file, content of schema.prisma etc.) running yarn rw dev results with:

nik$ yarn rw dev
gen | Generating TypeScript definitions and GraphQL schemas...
gen | 42 files generated
api | Building... Took 550 ms
api | Debugger listening on ws://127.0.0.1:18911/54079687-0331-4e22-8348-b1f9e76e61df
api | For help, see: https://nodejs.org/en/docs/inspector
api | Starting API Server...
api | {"level":30,"time":1688331254896,"pid":32363,"hostname":"Nikolajs-MacBook-Pro.local","side":"api","_":["api"],"port":8911,"p":8911,"apiRootPath":"/","rootPath":"/","root-path":"/","api-root-path":"/","$0":"node_modules/@redwoodjs/api-server/dist/index.js","msg":"In configureFastify hook for side: api"}
api | Importing Server Functions... 
api | /graphql 764 ms
api | /auth 2 ms
api | ...Done importing in 767 ms
api | Took 823 ms
api | API listening on http://localhost:8911/
api | GraphQL endpoint at /graphql
api | 16:54:15 🐛 undefined undefined Fastify server configuration 
api | 🗒 Custom
api | {
api |   "connectionTimeout": 0,
api |   "keepAliveTimeout": 72000,
api |   "maxRequestsPerSocket": 0,
api |   "requestTimeout": 0,
api |   "requestIdHeader": "request-id",
api |   "requestIdLogLabel": "reqId",
api |   "disableRequestLogging": false,
api |   "bodyLimit": 1048576,
api |   "caseSensitive": true,
api |   "allowUnsafeRegex": false,
api |   "ignoreTrailingSlash": false,
api |   "ignoreDuplicateSlashes": false,
api |   "jsonShorthand": true,
api |   "maxParamLength": 100,
api |   "onProtoPoisoning": "error",
api |   "onConstructorPoisoning": "error",
api |   "pluginTimeout": 10000,
api |   "http2SessionTimeout": 72000,
api |   "exposeHeadRoutes": true
api | } 
api | 🗒 Custom
api | {
api |   "time": 1688331255689,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "Fastify server configuration"
api | }
api | 16:54:15 🐛 undefined undefined Registered plugins 
api | bound root 810 ms
api | ├── bound _after 5 ms
api | ├── @fastify/url-data 0 ms
api | ├── fastify-raw-body 1 ms
api | ├── bound _after 0 ms
api | ├── bound _after 0 ms
api | ├── bound _after 1 ms
api | ├── bound _after 0 ms
api | └── bound _after 0 ms
api |  
api | 🗒 Custom
api | {
api |   "time": 1688331255690,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "Registered plugins \nbound root 810 ms\n├── bound _after 5 ms\n├── @fastify/url-data 0 ms\n├── fastify-raw-body 1 ms\n├── bound _after 0 ms\n├── bound _after 0 ms\n├── bound _after 1 ms\n├── bound _after 0 ms\n└── bound _after 0 ms\n"
api | }
api | 16:54:15 🌲 undefined undefined Server listening at http://[::]:8911 
api | 🗒 Custom
api | {
api |   "time": 1688331255694,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "Server listening at http://[::]:8911"
api | }
web | assets by path static/js/*.js 6.62 MiB
web |   asset static/js/app.bundle.js 3.1 MiB [emitted] (name: app) 1 related asset
web |   asset static/js/LoginPage.chunk.js 447 KiB [emitted] (name: LoginPage) 1 related asset
web |   asset static/js/SignupPage.chunk.js 447 KiB [emitted] (name: SignupPage) 1 related asset
web |   asset static/js/ResetPasswordPage.chunk.js 425 KiB [emitted] (name: ResetPasswordPage) 1 related asset
web |   asset static/js/ForgotPasswordPage.chunk.js 404 KiB [emitted] (name: ForgotPasswordPage) 1 related asset
web |   asset static/js/PostEditPostPage.chunk.js 367 KiB [emitted] (name: PostEditPostPage) 1 related asset
web |   asset static/js/PostNewPostPage.chunk.js 366 KiB [emitted] (name: PostNewPostPage) 1 related asset
web |   asset static/js/ContactPage.chunk.js 346 KiB [emitted] (name: ContactPage) 1 related asset
web |   asset static/js/PostPostsPage.chunk.js 260 KiB [emitted] (name: PostPostsPage) 1 related asset
web |   asset static/js/PostPostPage.chunk.js 236 KiB [emitted] (name: PostPostPage) 1 related asset
web |   + 5 assets
web | asset favicon.png 1.83 KiB [emitted] [from: public/favicon.png] [copied]
web | asset index.html 456 bytes [emitted]
web | asset robots.txt 24 bytes [emitted] [from: public/robots.txt] [copied]
web | Entrypoint app 3.15 MiB (3.09 MiB) = static/js/runtime-app.bundle.js 51 KiB static/js/app.bundle.js 3.1 MiB 2 auxiliary assets
web | orphan modules 413 KiB [orphan] 116 modules
web | runtime modules 34.3 KiB 19 modules
web | modules by path ../node_modules/ 2.78 MiB 951 modules
web | modules by path ./src/ 198 KiB
web |   modules by path ./src/pages/ 104 KiB 14 modules
web |   modules by path ./src/components/ 38.5 KiB 10 modules
web |   modules by path ./src/*.css 40.4 KiB
web |     ./src/scaffold.css 2.88 KiB [built] [code generated]
web |     + 3 modules
web |   modules by path ./src/*.js 9.96 KiB
web |     ./src/App.js 1.78 KiB [built] [code generated]
web |     + 2 modules
web |   modules by path ./src/layouts/ 4.9 KiB
web |     ./src/layouts/BlogLayout/BlogLayout.js 2.99 KiB [built] [code generated]
web |     ./src/layouts/PostsLayout/PostsLayout.js 1.91 KiB [built] [code generated]
web | webpack 5.81.0 compiled successfully in 8110 ms
api | 16:54:20 🌲 undefined undefined incoming request GET xxx /auth?method=getToken 
api | 🗒 Custom
api | {
api |   "time": 1688331260843,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "reqId": "req-1",
api |   "req": {
api |     "method": "GET",
api |     "url": "/auth?method=getToken",
api |     "hostname": "localhost:8910",
api |     "remoteAddress": "::1",
api |     "remotePort": 58184
api |   },
api |   "msg": "incoming request"
api | }
api | 16:54:20 🌲 undefined undefined request completed 20ms 
api | 🗒 Custom
api | {
api |   "time": 1688331260864,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "reqId": "req-1",
api |   "res": {
api |     "statusCode": 200
api |   },
api |   "msg": "request completed"
api | }
api | 16:54:20 🌲 undefined undefined incoming request POST xxx /graphql 
api | 🗒 Custom
api | {
api |   "time": 1688331260940,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "reqId": "req-2",
api |   "req": {
api |     "method": "POST",
api |     "url": "/graphql",
api |     "hostname": "localhost:8910",
api |     "remoteAddress": "::1",
api |     "remotePort": 58185
api |   },
api |   "msg": "incoming request"
api | }
api | 16:54:20 🐛 undefined undefined Parsing request to extract GraphQL parameters 
api | 🗒 Custom
api | {
api |   "time": 1688331260949,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "Parsing request to extract GraphQL parameters"
api | }
api | 16:54:20 🐛 undefined undefined Processing GraphQL Parameters 
api | 🗒 Custom
api | {
api |   "time": 1688331260950,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "Processing GraphQL Parameters"
api | }
api | 16:54:20 🐛 undefined graphql-server GraphQL execution started: BlogPostsQuery 
api | 🗒 Custom
api | {
api |   "time": 1688331260965,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "GraphQL execution started: BlogPostsQuery"
api | }
api | 16:54:20 🌲 undefined undefined Starting a postgresql pool with 1 connections. 
api | 🗒 Custom
api | {
api |   "time": 1688331260973,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "prisma": {
api |     "clientVersion": "4.13.0"
api |   },
api |   "timestamp": "2023-07-02T20:54:20.973Z",
api |   "target": "quaint::pooled",
api |   "msg": "Starting a postgresql pool with 1 connections."
api | }
api | 16:54:20 🚨 undefined undefined 
api | Invalid `db.post.findMany()` invocation in
api | /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/api/src/services/posts/posts.js:4:18
api | 
api |   1 import { db } from 'src/lib/db'
api |   2 
api |   3 export const posts = () => {
api | → 4   return db.post.findMany(
api | Database `rw-auth` does not exist on the database server at `localhost:5432`. 
api | 🗒 Custom
api | {
api |   "time": 1688331260986,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "prisma": {
api |     "clientVersion": "4.13.0"
api |   },
api |   "timestamp": "2023-07-02T20:54:20.986Z",
api |   "target": "post.findMany",
api |   "msg": "\nInvalid `db.post.findMany()` invocation in\n/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/api/src/services/posts/posts.js:4:18\n\n  1 import { db } from 'src/lib/db'\n  2 \n  3 export const posts = () => {\n→ 4   return db.post.findMany(\nDatabase `rw-auth` does not exist on the database server at `localhost:5432`."
api | }
api | 16:54:20 🌲 undefined undefined request completed 52ms 
api | 🗒 Custom
api | {
api |   "time": 1688331260993,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "reqId": "req-2",
api |   "res": {
api |     "statusCode": 200
api |   },
api |   "msg": "request completed"
api | }
api | 16:54:20 🚨 undefined graphql-server 
api | Invalid `db.post.findMany()` invocation in
api | /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/api/src/services/posts/posts.js:4:18
api | 
api |   1 import { db } from 'src/lib/db'
api |   2 
api |   3 export const posts = () => {
api | → 4   return db.post.findMany(
api | Database `rw-auth` does not exist on the database server at `localhost:5432`. 
api | 
api | 🚨 GraphQLError Info
api | 
api | {
api |   "path": [
api |     "articles"
api |   ],
api |   "locations": [
api |     {
api |       "line": 2,
api |       "column": 3
api |     }
api |   ],
api |   "extensions": {}
api | }
api |  
api | 🥞 Error Stack
api | 
api | Error: 
api | Invalid `db.post.findMany()` invocation in
api | /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/api/src/services/posts/posts.js:4:18
api | 
api |   1 import { db } from 'src/lib/db'
api |   2 
api |   3 export const posts = () => {
api | → 4   return db.post.findMany(
api | Database `rw-auth` does not exist on the database server at `localhost:5432`.
api |     at pn.handleRequestError (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:176:6750)
api |     at pn.handleAndLogRequestError (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:176:5907)
api |     at /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:179:3087
api |     at async /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:179:3294
api |     at async t._executeRequest (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:179:10755)
api |     at async t._request (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:179:10484)
api |     at async field.resolve (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@envelop/on-resolve/cjs/index.js:33:42)
api |     at async field.resolve (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@envelop/on-resolve/cjs/index.js:33:42)
api |     at async field.resolve (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@envelop/on-resolve/cjs/index.js:33:42)
api |     at async Promise.all (index 0)
api |  
api | 🗒 Custom
api | {
api |   "time": 1688331260990,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "\nInvalid `db.post.findMany()` invocation in\n/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/api/src/services/posts/posts.js:4:18\n\n  1 import { db } from 'src/lib/db'\n  2 \n  3 export const posts = () => {\n→ 4   return db.post.findMany(\nDatabase `rw-auth` does not exist on the database server at `localhost:5432`."
api | }
api | 16:54:20 🚨 undefined undefined 
api | Invalid `db.post.findMany()` invocation in
api | /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/api/src/services/posts/posts.js:4:18
api | 
api |   1 import { db } from 'src/lib/db'
api |   2 
api |   3 export const posts = () => {
api | → 4   return db.post.findMany(
api | Database `rw-auth` does not exist on the database server at `localhost:5432`. 
api | 
api | 🚨 GraphQLError Info
api | 
api | {
api |   "path": [
api |     "articles"
api |   ],
api |   "locations": [
api |     {
api |       "line": 2,
api |       "column": 3
api |     }
api |   ],
api |   "extensions": {}
api | }
api |  
api | 🥞 Error Stack
api | 
api | Error: 
api | Invalid `db.post.findMany()` invocation in
api | /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/api/src/services/posts/posts.js:4:18
api | 
api |   1 import { db } from 'src/lib/db'
api |   2 
api |   3 export const posts = () => {
api | → 4   return db.post.findMany(
api | Database `rw-auth` does not exist on the database server at `localhost:5432`.
api |     at pn.handleRequestError (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:176:6750)
api |     at pn.handleAndLogRequestError (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:176:5907)
api |     at /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:179:3087
api |     at async /Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:179:3294
api |     at async t._executeRequest (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:179:10755)
api |     at async t._request (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@prisma/client/runtime/library.js:179:10484)
api |     at async field.resolve (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@envelop/on-resolve/cjs/index.js:33:42)
api |     at async field.resolve (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@envelop/on-resolve/cjs/index.js:33:42)
api |     at async field.resolve (/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/node_modules/@envelop/on-resolve/cjs/index.js:33:42)
api |     at async Promise.all (index 0)
api |  
api | 🗒 Custom
api | {
api |   "time": 1688331260991,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "\nInvalid `db.post.findMany()` invocation in\n/Users/nik/dev/learning/Redwoodjs/redwood-tutorial/api/src/services/posts/posts.js:4:18\n\n  1 import { db } from 'src/lib/db'\n  2 \n  3 export const posts = () => {\n→ 4   return db.post.findMany(\nDatabase `rw-auth` does not exist on the database server at `localhost:5432`."
api | }
api | 16:54:20 🐛 undefined undefined Processing GraphQL Parameters done. 
api | 🗒 Custom
api | {
api |   "time": 1688331260991,
api |   "pid": 32363,
api |   "hostname": "Nikolajs-MacBook-Pro.local",
api |   "msg": "Processing GraphQL Parameters done."
api | }

My suspicion that this problem is based on insufficient access right, however, I am confused by the error message

16:54:15 🐛 undefined Fastify server configuration

Assuming that this discussion (including the solution) would be of interest, I will stuff it in the solutions category in https://community.redwoodjs.com/

2

How about running Postgres in a Docker?

  • Install Docker

  • in Docker Desktop, go to Containers and Run Postgres installation (or via terminal docker pull postgres)

  • go to images, choose Postgres Run and add optional settings:

    1. Set port to YOURPORT and
    2. environment variables: POSTGRES_PASSWORD – YOURPASSWORD
    3. default user will be postgres, as you state, so YOURUSER = postgres

  • in .env file write DATABASE_URL=postgres://YOURUSER:YOURPASSWORD@localhost:YOURPORT/rw_auth
    and in schema.prisma use url = env("DATABASE_URL")

I hope this works. Only issue might be, that the setup of postgres in docker changed a bit.

2 Likes
3

Thank you @dennemark for this advice, which I am pretty sure would work (I will indeed verify and confirm that). Switching from local installation to Docker based database, looks to me like a defeat (as any perfect perfectionist would agree), so I will also pursue my current theory about my problems.

The official Redwood document Local Postgres Setup describes the Database creation using the client (terminal) tool psql, in a manner that presumes developer knowledge about that tool. As I am not that developer, I am using the GUI equivalent of the psql - pgAdmin 4.

Trying to extrapolate from Local Postgres Setup I believe that I failed - creating databases with pgAdmin resulted with failed access by my app. I will post more once I switch to using psql only

4

Hey @adriatic, I also found setting up postgres locally to be a massive pain.
I’ve sort of got the hang of it now but have to refer back to these two articles to remind me how to do it:
https://www.codementor.io/@engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb#3-configuring-postgres
&

I also made some notes of some useful commands to remind me how to do a couple of things with psql :

Access psql with:
psql postgres

List Roles:
\du

exit psql with:
\q

Create a new database user with:
CREATE ROLE username WITH LOGIN PASSWORD 'quoted password' [OPTIONS];
(don’t forget the trailing semicolon)

Give the new role permission to create a db:
ALTER ROLE patrick CREATEDB;

Then Quit out (‘\q’) again
Login as the new user:
psql postgres -U username

Create the DB:
CREATE DATABASE super_awesome_application;

  • \list: lists all the databases in Postgres
  • \connect: connect to a specific database
  • \dt: list the tables in the currently connected database

I hope that some of that is useful

1 Like
5

Many thanks, @mfosker - @dennemark’s and your advices will hopefully allow me to write the verified/tested article that will augment the Local Postgres Setup article - the MacOS side of it and post it in the Solutions collection.

After all, PostgresQL is quite important to most of RW Community members as one of the top four contenders and certainly first, if one cares about its open source attribute.

Most of the pain in the local installation is the consequence of incomplete and sometimes mutually “confronting” documentation. At this point, I will focus primarily on psql - only after having this written I may include pgAdmin GUI tool…

6

I’ve had lots of success with https://postgresapp.com/ for OSX and then combine with https://tableplus.com/ for admin.

2 Likes
7

I hoped you would pitch in, as I have a big respect for your opinions. My setup should work (using the Redwood app configured as described in Local Postgres Setup | RedwoodJS Docs – and using pgAmin V4 to create and configure the database). The problem is that the application “does not see” the databases created by pgAdmin. If instead of pgAdmin, I issue the command $ createdb $(whoami) meaning that I ought to use psql instead of pgAmin - and I have not suffered enough to give up on that pgAdmin based creating and managing the database.

All this problems are caused by “paths” to objects and access rights.

8

@mfosker I just reviewed your instructions and determined that you used psql for configuring the database. I am still stubbornly trying to use current pgAdmin that comes with the PostgreSQL installation, so I will confirm your recipe once I give up on pgAdmin. :sleepy:

9

Even if you probably don’t care, I’ve been using docker since like forever for this. As local postgres setups are annoying

Here’s my docker-compose.yml with the other services removed

version: "3"
services:
  database:
    image: postgres:15
    # NOTE: avoid collision with local postgres port
    ports:
      - 6543:5432
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    restart: unless-stopped

I start this via docker-compose up database each morning.

Then connect with pgAdmin:
Hostname: localhost
Port: 6543

And that’s it

2 Likes
10

Wow - this certainly seems like the best way to use locally run postgres database. @dennemark suggested nearly identical approach.

Thank you, I am now convinced that I will use it as my first variant of the RedwoodJS tutorial app using postgres (see adriatic postgres for more details (today you might find nothing yet, as I am about to start writing it))

2 Likes
11

@razzeee I have a question on sequencing in time about the content of your docker-compose: is it true that once the docker-compose command instantiates the Postgres database, it passes dynamically the data

     POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}

In the non-docker-based use of Postgres, I would create the database and the DB admin role once and define the password at that same time - and that use such configured database whenever running my app (which identifies itself using the above data).

Running the docker-compose up every day in the morning and docker-compose down when you close your workday is a dynamic operation so I am hoping that docker-compose down would not result in the loss of database data (I used Docker for a brief period a few years back and forgot the persistence behavior.

I am lastly assuming that you use pgAmin also via Docker. True?

12

Hey @adriatic

The variables you listed above are only important on the first run AFAIK. So if you have a docker volume created it and change one of those variables, it won’t take effect, as long as you don’t recreate the volume.

docker-compose down shouldn’t break anything / loose data.

To reset you would need something like docker-compose down database -rmi all -v that will also get rid of your volumes (and thus your data)

13

:grinning: (thanks)

1 Like