almost there

[smnorris]

pg setup on linux is a bit more involved than on windows but it looks like you almost have it, you're connecting to the db. My script for pg12

# change to postgres user, alter password
sudo su - postgres
psql -c "alter user postgres with password 'mynewpassword'"
# create a new general user for the db
createuser -P -s -e mynewuser
# create new db to do stuff in
createdb mydb
# exit postgres user
exit

# save pg typing
echo 'export PGDATA=/etc/postgresql/12/main' >> ~/.profile
echo 'export PGHOST=localhost' >> ~/.profile
echo 'export PGPORT=5432' >> ~/.profile
echo 'export PGDATABASE=mydb' >> ~/.profile
echo 'export PGUSER=mynewuser' >> ~/.profile
echo 'export PGPASSWORD=mypassword' >> ~/.profile
echo 'export DATABASE_URL=postgresql://$PGUSER:$PGPASSWORD@$PGHOST:$PGPORT/$PGDATABASE' >> ~/.profile
echo "export PGOGR='host=$PGHOST user=$PGUSER dbname=$PGDATABASE password=$PGPASSWORD port=$PGPORT'" >> ~/.profile
source .profile

# open up the database to the world
echo "local  all  all                trust" >> $PGDATA/pg_hba.conf
echo "host   all  all  127.0.0.1/32  trust" >> $PGDATA/pg_hba.conf
echo "host all all 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
echo "listen_addresses = '*'" >> /etc/postgresql/12/main/postgresql.conf

[smnorris]

I think the key here is creating the new db user. Don't use the default postgres user. I think postgres prompts you for a pwd when you do createuser -P -s -e mynewuser.

[NewGraphEnvironment]

oh buddy. Getting there is right. Thank you!

Question:

For your line echo "host all all 127.0.0.1/32 trust" >> $PGDATA/pg_hba.conf

Do you think I should sub in 172.18.155.89 or is the lo: inet 127.0.0.1 the same on my wsl?

[smnorris]

I think that line is saying don't ask for a password if logging on from localhost, so it isn't really needed if you're setting a password and provide it each time you access the db.

If you keep the line, I'm not much help - I don't know if swapping that ip is the way to go or not without consulting the docs https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

[smnorris]

I don't know how wsl works but pretty sure you would leave it as 127.0.0.1

[NewGraphEnvironment]

ok. Thanks. Ya, I left it. Well after 4 years I think I might have bcdata bc2pg running! Thanks so much. I was losing it earlier. God I'm slow.

On to the real stuff... Hopefully... 🙏🏼 . Thank you 👏🏼 👏🏼 👏🏼 🙇🏼

[smnorris]

Well the part that I wonder is whether you can connect to the db that you've installed on wsl from windows and R... and how you might go about it. Is host still localhost??

[NewGraphEnvironment]

ya. Host is still localhost. Good question. I wonder if I should install rand rstudio into wsl and work right out of there. I'll look into it

[smnorris]

Still localhost is very good. I connect to pg from windows in a vm on my mac and the networking is a pain to manage.

Now that you've got it working... I could also have suggested Docker, that can work really well for getting a db set up fast.

[NewGraphEnvironment]

yup. No problem! Changed the port and user and 💥 . Stoked

[NewGraphEnvironment]

ya. I think in the long run Docker might have been a better plan. Would that have allowed clean collab without changing line endings and the rest of that BS? The OS stuff is ridiculous. Have you seen Github Codespaces? The groms aren't even going to know what we went through.

[smnorris]

No, because my scripts are bash you'd still have to change the line endings. It would just save installing & configuring postgres because the container comes with db pre-configured with a common setup. I'll point you to an example once I've got the repo for this other project up.

Haven't seen that, looks pretty cool

[smnorris]

https://github.com/smnorris/transportingharvestedbiomass/blob/main/setup.sh#L7