Skip to main content

postgres

postgres configuration on server​

1. Remove any existing repository file:​

sudo rm -f /etc/yum.repos.d/pgdg-redhat-all.repo

2. Create the repository file again:​

sudo nano /etc/yum.repos.d/pgdg-redhat-all.repo

3. Add the following content to the file:​

[pgdg13]
name=PostgreSQL 13 for RHEL/CentOS 8 - x86_64
baseurl=https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8-x86_64/
enabled=1
gpgcheck=0

4. Clear the yum cache:​

sudo yum clean all
sudo yum makecache

5. Install PostgreSQL:​

sudo yum install -y postgresql13-server postgresql13

6. Initialize the PostgreSQL database:​

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

7. Start and enable PostgreSQL service:​

sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13

8. Switch to the PostgreSQL user​

sudo -i -u postgres

9. Create a new PostgreSQL database:​

Use the createdb command to create a new database.

createdb your_database_name

10. Create a new PostgreSQL user:​

Use the createuser command to create a new user.

createuser --interactive

Follow the prompts to set up the new user.

11. Set a password for the PostgreSQL user:​

Access the PostgreSQL prompt and set a password for the new user.

psql
ALTER USER your_username WITH PASSWORD 'your_password';
\q

12. Grant privileges to the new user:​

Grant the necessary privileges to the new user on the new database

psql
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;
\q

13. Configure PostgreSQL to allow remote connections (optional):​

Edit the postgresql.conf file to listen on all IP addresses.

sudo nano /var/lib/pgsql/13/data/postgresql.conf

Find the line #listen_addresses = 'localhost' and change it to

listen_addresses = '*'

Edit the pg_hba.conf file to allow connections from your IP address.

sudo nano /var/lib/pgsql/13/data/pg_hba.conf

Add the following line at the end of the file:

host    all             all             your_ip_address/32            md5

13. Restart PostgreSQL service:​

sudo systemctl restart postgresql-13

configuring postgres with Nextjs​

To use a PostgreSQL database with Prisma in a Next.js application, follow these steps:

1. Install Dependencies​

install Prisma and the PostgreSQL client:

npm install @prisma/client --legacy-peer-deps
npm i -D prisma --legacy-peer-deps

2. Initialize Prisma​

initialize Prisma in your project:

npx prisma init

This will create a prisma directory with a schema.prisma file and a .env file.

3. Configure Prisma​

Edit the .env file to include your PostgreSQL connection string:

DATABASE_URL="postgresql://user:password@localhost:5432/mydatabase"

Replace user, password, localhost, 5432, and mydatabase with your actual database credentials.

Edit the prisma/schema.prisma file to define your data model. For example:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id    Int     @id @default(autoincrement())
  name  String
  email String  @unique
}

4. Generate Prisma Client​

Run the following command to generate the Prisma client:

npx prisma generate

5. synchronize Prisma schema​

the npm prisma db push command is used to synchronize your Prisma schema with your database.

npx prisma db push

6. Prisma Studio​

The npx prisma studio command is used to launch Prisma Studio, a web-based GUI for managing your database.

npx prisma studio