Install psql using docker:
alias psql="docker run -e POSTGRES_PASSWORD=password -it --rm --name postgresql postgres:15 psql"
Connect to the PostgreSQL server: Use the psql command to connect to your PostgreSQL server. Since you’ve exposed the PostgreSQL port (5432) to your host in the Docker Compose file, you can connect to it using casaos-ip as the host and 5432 as the port number. The following command connects to the PostgreSQL server using the casaos user:
psql -h [casaos-ip] -p 5432 -U bigbear -W -d casaos
This will prompt you for the password. Enter the password (password based on your Docker Compose configuration) when prompted.
Show all databases:
\l
Use the casaos database (as specified in your Docker Compose):
\c casaos
List tables in the database:
\dt
Exit the psql client: You can exit the psql client using the following command:
\q
Troubleshoot
Raspberry PI
If you’re getting this error:
docker: permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Post "http://%2Fvar%2Frun%2Fdocker.sock/v1.24/containers/create?name=postgresql": dial unix /var/run/docker.sock: connect: permission denied.
See 'docker run --help'.
The error message you’re encountering indicates that your current user does not have the necessary permissions to communicate with the Docker daemon. By default, Docker requires root privileges.
On a Raspberry Pi, the default user is pi, and this user has sudo privileges. When you execute sudo -i, you will be granted an interactive shell with root user privileges. While in this root shell, you don’t need to prefix commands with sudo because you’re already operating with superuser privileges.
Here’s what you typically do:
- Open a terminal or connect to your Raspberry Pi via SSH.
- Type
sudo -iand press Enter. - You’ll be asked for the pi user’s password. Once you enter it, you’ll be given a root shell.