This guide provides step-by-step instructions to install PostgreSQL on various operating systems.
- Prerequisites
- Installation on Ubuntu
- Installation on macOS
- Post-Installation Setup
- Troubleshooting
- PgAi Extension for PostgreSQL
- Contributing
- Contact
- Ensure you have administrative privileges on your system.
- An internet connection to download necessary files.
-
Update the package list:
sudo apt update
-
Install PostgreSQL:
sudo apt install postgresql postgresql-contrib
-
Verify the installation:
psql --version
-
Install Homebrew if you haven't already:
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
-
Install PostgreSQL using Homebrew:
brew install postgresql
-
Start the PostgreSQL service:
brew services start postgresql
-
Verify the installation:
psql --version
-
Switch to the
postgres
user:sudo -i -u postgres
-
Open the PostgreSQL prompt:
psql
-
Create a new user:
CREATE USER yourusername WITH PASSWORD 'yourpassword';
-
Grant privileges to the new user:
ALTER USER yourusername WITH SUPERUSER;
-
Exit the PostgreSQL prompt:
\q
-
Switch to the
postgres
user if not already done:sudo -i -u postgres
-
Create a new database:
createdb yourdatabase
- Unable to connect to the server: Ensure the PostgreSQL server is running and listening on the correct port.
- Authentication failed: Verify the username and password are correct.
- Permission denied: Ensure you have the necessary privileges to perform the desired action.
For more detailed troubleshooting, refer to the PostgreSQL documentation.
Feel free to contribute to this guide by submitting a pull request or opening an issue on our GitHub repository.
Postgres - AI Extension (PgAi): PgAi integrates predictive analytics directly into PostgreSQL, allowing users to execute "predictive queries" seamlessly. This extension brings the power of AI-driven predictions into the database, enabling data-driven decisions and advanced analytics without the need for external tools.
-
Prerequisites:
Ensure you have PostgreSQL installed. Download from the official PostgreSQL website. -
Cloning the Repository:
Clone the pgai repository and navigate to the directory:git clone [email protected]:moiz697/PgAi.git cd pgai
-
Create .env File:
Create a.env
file in the root directory of your project with the following content:PORT=5432 USERNAME=yourusername DATABASE=yourdatabase PASSWORD=yourpassword
-
Export PostgreSQL Path:
Export the PostgreSQL bin directory to your PATH. Replace/path/to/postgres
with your PostgreSQL installation path:export PATH=/path/to/postgres/bin:$PATH
-
Build and Install the Extension:
make && make install
-
Run PostgreSQL:
Ensure PostgreSQL is running:pg_ctl start
-
Create the Extension:
Connect to your PostgreSQL instance and create the pgai extension:CREATE EXTENSION pgai;
-
Creating the Stock Table:
Create theEXAMPLE_stock
table to store stock data:CREATE TABLE IF NOT EXISTS EXAMPLE_stock ( date DATE PRIMARY KEY, open DOUBLE PRECISION, high DOUBLE PRECISION, low DOUBLE PRECISION, close DOUBLE PRECISION, adj_close DOUBLE PRECISION, volume BIGINT );
-
Downloading Stocks from Yahoo Finance:
Visit Yahoo Finance to download stock data. -
Importing Stock Data into PostgreSQL:
Use theCOPY
command to import the downloaded stock data into the PostgreSQL table:COPY EXAMPLE_stock(date, open, high, low, close, adj_close, volume) FROM '/path/to/example.csv' DELIMITER ',' CSV HEADER;
We provide four models:
- LSTM
- Prophet
- ARIMA
- SARIMA
- Add Configurations in PostgreSQL:
Create a table to store database connection details:CREATE TABLE IF NOT EXISTS db_config ( key TEXT PRIMARY KEY, value TEXT NOT NULL ); INSERT INTO db_config (key, value) VALUES ('db_host', 'localhost'), ('db_port', '5432'), ('db_name', 'yourdatabase'), ('db_user', 'yourusername'), ('db_password', 'yourpassword');
You can also write your own model and make changes in the pgai--1.0
file.
To use the pgai extension, follow these steps:
-
Load the Extension:
CREATE EXTENSION pgai;
-
Run Predictive Queries: Use the provided models to perform predictive analytics directly within your PostgreSQL database. Example query:
SELECT * FROM EXAMPLE_stock('2025-09-21');
Refer to the pgai
documentation for detailed examples and usage patterns.
The PgAi extension allows the integration of pseudo columns that fetch predictive values from trained models. While the primary example provided is stock data prediction, the same methodology can be applied to other datasets with minimal modifications. For instance, you can use PgAi for:
- Weather Forecasting: Predict future weather conditions based on historical data.
- Sales Projections: Forecast future sales using past sales data.
- Resource Allocation: Predict future resource needs in logistics or supply chain management.
By adjusting the models and the SQL functions, you can adapt the PgAi extension to various domains. For any help regarding this, feel free to reach out to the team. We are happy to help you.
- Integrated Predictive Analytics: PgAi brings predictive analytics directly into PostgreSQL, eliminating the need for external tools or platforms. This seamless integration simplifies the workflow and enhances performance.
- Flexibility: The extension is designed to be adaptable to various types of data. Whether it's stock prices, weather forecasting, or sales projections, PgAi can be customized to meet specific requirements.
- Efficiency: By running predictive queries within the database, PgAi reduces data transfer overhead and speeds up the analysis process.
- Ease of Use: With simple SQL commands, users can perform complex predictive analytics, making advanced data science techniques accessible to a broader audience.
We welcome contributions. Fork the repository and submit pull requests.
For any questions or feedback, please open an issue on the GitHub repository or contact us at [email protected].
LinkedIn Profiles:
Happy Predicting with pgai!