mddw_stop_tb=# VACUUM FULL ANALYZE; VACUUM mddw_stop_tb=# [root@162-214-101-42 ~]# sudo -u postgres psql [root@162-214-101-42 ~]# sudo dnf search postgis Repository pgdg12 is listed more than once in the configuration Last metadata expiration check: 1:32:55 ago on Mon 20 Jan 2025 04:49:15 PM UTC. ================================================== Name Exactly Matched: postgis =================================================== postgis.x86_64 : Geographic Information Systems Extensions to PostgreSQL ================================================= Name & Summary Matched: postgis ================================================== postgis-client.x86_64 : The CLI clients for PostGIS postgis-docs.x86_64 : Extra documentation for PostGIS postgis-gui.x86_64 : The shp2pgsql-gui utility for PostGIS postgis-upgrade.x86_64 : Support for upgrading Postgis postgis-utils.x86_64 : The utils for PostGIS postgis30_12-client.x86_64 : Client tools and their libraries of PostGIS postgis30_12-devel.x86_64 : Development headers and libraries for PostGIS postgis30_12-docs.x86_64 : Extra documentation for P [root@162-214-101-42 ~]# sudo dnf install postgis35_16 postgis35_16-utils (psql 16 on amla linux) Repository pgdg12 is listed more than once in the configuration Last metadata expiration check: 1:34:02 ago on Mon 20 Jan 2025 04:49:15 PM UTC. Dependencies resolved. ==================================================================================================================================== Package Architecture Version Repository Size ==================================================================================================================================== Installing: postgis35_16 x86_64 3.5.1-1PGDG.rhel9 pgdg16 4.0 M postgis35_16-utils x86_64 3.5.1-1PGDG.rhel9 pgdg16 42 k Upgrading: SFCGAL-libs x86_64 2.0.0-1PGDG.rhel9 pgdg-common 2.4 M proj95 x86_64 9.5.1-1PGDG.rhel9 pgdg-common 2.9 M Installing dependencies: gdal310-libs x86_64 3.10.1-1PGDG.rhel9 pgdg-common 9.4 M postgresql16-contrib x86_64 16.6-1PGDG.rhel9 pgdg16 724 k Downgrading: SFCGAL x86_64 1.5.0-1.el9 epel 2.1 M Transaction Summary ========================= dwh_onedataconnect_nigeria_uat postgres=# postgres=# CREATE USER nigeria_user WITH ENCRYPTED PASSWORD 'pul0Jl^8>y8b,!'; CREATE ROLE postgres=# CREATE DATABASE dwh_onedataconnect_nigeria_uat OWNER nigeria_user; CREATE DATABASE postgres=# GRANT ALL PRIVILEGES ON DATABASE dwh_onedataconnect_nigeria_uat TO nigeria_user; GRANT postgres=# \c prodcdicdb You are now connected to database "prodcdicdb" as user "postgres". prodcdicdb=# car_dwh=# SELECT extname FROM pg_extension; pg_hba.conf for access db for all n/w ___________________________----------------------------------------------------------------------------- # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all md5 host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5 host cowintestdb all 10.0.7.5/32 md5 host replication all 10.0.7.5/32 md5 # Remove old specific IP entries and allow connections from anywhere for all databases and users hostnossl all all 0.0.0.0/0 md5 hostnossl all all ::/0 md5 ------------------------------------------------------------------------------------------------- ******************* Mongo DB Command ******************* db.dropDatabase() > use qasales2 > db.createUser( { user: "qasusr2", pwd: passwordPrompt(), roles: [ {role: "dbOwner" , db:"qasales2"} ] } ) #MongoDB spring.data.mongodb.authentication-database=devngnewdb spring.data.mongodb.database=devngnewdb spring.data.mongodb.port=27017 spring.data.mongodb.host=localhost spring.data.mongodb.username=devngnewdbusr spring.data.mongodb.password=INZ&CKzX~YGHT mongorestore --host=localhost --port=27017 -u qasusr2 --authenticationDatabase qasales2 -d qasales2 qasales2 mongorestore --host localhost --port 27020 --username root --password admin@123 --authenticationDatabase admin --db qasales2 qasales2 mongodump --host localhost --port 27017 --db devngnewdb --username devngnewdbusr --password 'INZ&CKzX~YGHT' --out devngnewdb-bkp-28-01-2025 # MongoDB spring.data.mongodb.authentication-database=uatv1db spring.data.mongodb.database=uatv1db spring.data.mongodb.port=27017 spring.data.mongodb.host=localhost spring.data.mongodb.username=uatv1usr1 spring.data.mongodb.password=YCZ&*90^4Ge2 ********************* DB FOR DEV V1 -live ******************** DBName- dev-v1livedb UserName- dev-v1liveusr Password - KhrhtbzX~YBi6 mongorestore --host=localhost --port=27017 -u dev-v1liveusr --authenticationDatabase dev-v1livedb -d dev-v1livedb nxtgenimonlive > use dev-v1livedb > db.createUser( { user: "dev-v1liveusr", pwd: passwordPrompt(), roles: [ {role: "dbOwner" , db:"dev-v1livedb"} ] } ) Which Format Should You Use? For small databases: Use .sql, as it's easy to modify. For large production databases: Use .backup (fast restore, best compatibility). For extremely large databases (100GB+): Use .dump with parallel restore. For your 207GB production database, I recommend: pg_dump -U postgres -F c -f uatdpcbnewdb.backup uatdpcbnewdb pg_dump -U postgres -F c -f /root/dwh_onedataconnect_prod.backup dwh_onedataconnect_prod and restore it with: [ncderegistryuser@uatphilip ~]$ pg_dump -U ncderegisdbusr --clean --if-exists -O ncderegistrydb> ncderegistrydb-27feb-2025.sql pg_dump -U ncderegisdbusr -F c -f ncderegistrydb.backup ncderegistrydb pg_restore -U postgres -d dwh_onedataconnect_prod /root/dwh_onedataconnect_prod.backup pg_restore -U ncderegisdbusr -d ncderegistrydb --role=ncderegisdbusr -v ncderegistrydb.backup Let me know if you need more clarification! 🚀 Please save it for mongodb login on QA ( Password - admin@123 ) [root@devtest ~]# mongosh -u admin -p --authenticationDatabase admin Enter password: ********* Current Mongosh Log ID: 6799b3d70721fbe742544ca6 Connecting to: mongodb://@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&authSource=admin&appName=mongosh+2.3.8 Using MongoDB: 6.0.20 Using Mongosh: 2.3.8 For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/ Warning: Found ~/.mongorc.js, but not ~/.mongoshrc.js. ~/.mongorc.js will not be loaded. You may want to copy or rename ~/.mongorc.js to ~/.mongoshrc.js. Deprecation warnings: - Using mongosh on the current operating system is deprecated, and support may be removed in a future release. See https://www.mongodb.com/docs/mongodb-shell/install/#supported-operating-systems for documentation on supported platforms. test> show dbs admin 180.00 KiB config 72.00 KiB devngnewdb 2.69 GiB devv1nextdb 1.85 GiB local 96.00 KiB qasales2 923.04 MiB test> Azure cosmos Mongo [root@nextimon ~]# mongo "mongodb+srv://db-oneimpact.global.mongocluster.cosmos.azure.com/?tls=true" --username oneimpactdb MongoDB shell version v4.4.29 Enter password: connecting to: mongodb://fc-a9c1029e0eb4-000.global.mongocluster.cosmos.azure.com:10260/?compressors=disabled&gssapiServiceName=mongodb&ssl=true&tls=true {"t":{"$date":"2025-03-06T09:25:43.726Z"},"s":"I", "c":"NETWORK", "id":5490002, "ctx":"thread1","msg":"Started a new thread for the timer service"} Implicit session: session { "id" : UUID("1a43f21f-375c-48af-bd16-d6b5bb90d77f") } MongoDB server version: 6.0.0 WARNING: shell and server versions do not match mongos> mongos> root@OneImpact-Server-2:/home/user2onimpact/DB# mongorestore --uri="mongodb+srv://db-oneimpact.global.mongocluster.cosmos.azure.com/oneimpactdb?tls=true" --username oneimpactdb --drop --dir=/home/user2onimpact/DB/CIV_Database ********** PG-VECTOR EXTENSION INSTALLED ********** sudo git clone https://github.com/pgvector/pgvector.git sudo make PG_CONFIG=/usr/pgsql-16/bin/pg_config sudo make install PG_CONFIG=/usr/pgsql-16/bin/pg_config ls /usr/pgsql-16/share/extension/ | grep vector sudo sed -i "s/#shared_preload_libraries = ''/shared_preload_libraries = 'vector'/" /var/lib/pgsql/16/data/postgresql.conf sudo systemctl restart postgresql-16 sudo -u postgres psql Create the extension: CREATE EXTENSION IF NOT EXISTS vector; Error - Access denied for user 'postsafusr'@'106.213.80.233' (using password: YES) solution mysql> SELECT user, host FROM mysql.user WHERE user = 'postsafusr'; +------------+----------------+ | user | host | +------------+----------------+ | postsafusr | 103.252.7.235 | | postsafusr | 20.203.156.251 | | postsafusr | localhost | +------------+----------------+ 3 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> GRANT ALL PRIVILEGES ON *.* TO 'postsafusr'@'106.213.80.233' IDENTIFIED BY 'JLefK{nv;ziG' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SELECT user, host FROM mysql.user WHERE user = 'postsafusr'; +------------+----------------+ | user | host | +------------+----------------+ | postsafusr | 103.252.7.235 | | postsafusr | 106.213.80.233 | | postsafusr | 20.203.156.251 | | postsafusr | localhost | +------------+----------------+ 4 rows in set (0.00 sec) mysql> \q