One place for hosting & domains

      Server

      How to Manually Set Up a Prisma Server on Ubuntu 18.04


      The author selected the Electronic Frontier Foundation to receive a donation as part of the Write for DOnations program.

      Introduction

      Prisma is a data layer that replaces traditional object-relational mapping tools (ORMs) in your application. Offering support for both building GraphQL servers as well as REST APIs, Prisma simplifies database access with a focus on type safety and enables declarative database migrations. Type safety helps reduce potential code errors and inconsistencies, while the declarative database migrations allow you to store your datamodel in version control. These features help developers reduce time spent focused on setting up database access, migrations, and data management workflows.

      You can deploy the Prisma server, which acts as a proxy for your database, in a number of ways and host it either remotely or locally. Through the Prisma service you can access your data and connect to your database with the GraphQL API, which allows realtime operations and the ability to create, update, and delete data. GraphQL is a query language for APIs that allows users to send queries to access the exact data they require from their server. The Prisma server is a standalone component that sits on top of your database.

      In this tutorial you will manually install a Prisma server on Ubuntu 18.04 and run a test GraphQL query in the GraphQL Playground. You will host your Prisma setup code and development locally — where you will actually build your application — while running Prisma on your remote server. By running through the installation manually, you will have a deeper understanding and customizability of the underlying infrastructure of your setup.

      While this tutorial covers the manual steps for deploying Prisma on an Ubuntu 18.04 server, you can also accomplish this in a more automated way with Docker Machine by following this tutorial on Prisma’s site.

      Note: The setup described in this section does not include features you would normally expect from production-ready servers, such as automated backups and active failover.

      Prerequisites

      To complete this tutorial, you will need:

      Step 1 — Starting the Prisma Server

      The Prisma CLI is the primary tool used to deploy and manage your Prisma services. To start the services, you need to set up the required infrastructure, which includes the Prisma server and a database for it to connect to.

      Docker Compose allows you to manage and run multi-container applications. You’ll use it to set up the infrastructure required for the Prisma service.

      You will begin by creating the docker-compose.yml file to store the Prisma service configuration on your server. You’ll use this file to automatically spin up Prisma, an associated database, and configure the necessary details, all in one step. Once the file is spun up with Docker Compose, it will configure the passwords for your databases, so be sure to replace the passwords for managementAPIsecret and MYSQL_ROOT_PASSWORD with something secure. Run the following command to create and edit the docker-compose.yml file:

      • sudo nano docker-compose.yml

      Add the following content to the file to define the services and volumes for the Prisma setup:

      docker-compose.yml

      version: "3"
      services:
        prisma:
          image: prismagraphql/prisma:1.20
          restart: always
          ports:
            - "4466:4466"
          environment:
            PRISMA_CONFIG: |
              port: 4466
              managementApiSecret: my-secret
              databases:
                default:
                  connector: mysql
                  host: mysql
                  port: 3306
                  user: root
                  password: prisma
                  migrations: true
        mysql:
          image: mysql:5.7
          restart: always
          environment:
            MYSQL_ROOT_PASSWORD: prisma
          volumes:
            - mysql:/var/lib/mysql
      volumes:
        mysql:
      

      This configuration does the following:

      • It launches two services: prisma-db and db.
      • It pulls in the latest version of Prisma. As of this writing, that is Prisma 1.20.
      • It sets the ports Prisma will be available on and specifies all of the credentials to connect to the MySQL database in the databases section.

      The docker-compose.yml file sets up the managementApiSecret, which prevents others from accessing your data with knowledge of your endpoint. If you are using this tutorial for anything but a test deployment, you should change the managementAPIsecret to something more secure. When you do, be sure to remember it so that you can enter it later during the prisma init process.

      This file also pulls in the MySQL Docker image and sets those credentials as well. For the purposes of this tutorial, this Docker Compose file spins up a MySQL image, but you can also use PostgreSQL with Prisma. Both Docker images are available on Docker hub:

      Save and exit the file.

      Now that you have saved all of the details, you can start the Docker containers. The -d command tells the containers to run in detached mode, meaning they’ll run in the background:

      • sudo docker-compose up -d

      This will fetch the Docker images for both prisma and mysql. You can verify that the Docker containers are running with the following command:

      You will see an output that looks similar to this:

      CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS              PORTS                    NAMES
      24f4dd6222b1        prismagraphql/prisma:1.12   "/bin/sh -c /app/sta…"   15 seconds ago      Up 1 second         0.0.0.0:4466->4466/tcp   root_prisma_1
      d8cc3a393a9f        mysql:5.7                   "docker-entrypoint.s…"   15 seconds ago      Up 13 seconds       3306/tcp                 root_mysql_1
      

      With your Prisma server and database set up, you are now ready to work locally to deploy the Prisma service.

      Step 2 — Installing Prisma Locally

      The Prisma server provides the runtime environments for your Prisma services. Now that you have your Prisma server started, you can deploy your Prisma service. You will run these steps locally, not on your server.

      To start, create a separate folder to contain all of the Prisma files:

      Then move into that folder:

      You can install Prisma with Homebrew if you're using MacOS. To do this, run the following command to add the Prisma repository:

      You can then install Prisma with the following command:

      Or alternately, with npm:

      With Prisma installed locally, you are ready to bootstrap the new Prisma service.

      Step 3 — Creating the Configuration for a New Prisma Service

      After the installation, you can use prisma init to create the file structure for a new Prisma database API, which generates the files necessary to build your application with Prisma. Your endpoint will automatically be in the prisma.yml file, and datamodel.prisma will already contain a sample datamodel that you can query in the next step. The datamodel serves as the basis for your Prisma API and specifies the model for your application. At this point, you are only creating the files and the sample datamodel. You are not making any changes to the database until you run prisma deploy later in this step.

      Now you can run the following command locally to create the new file structure:

      After you run this command you will see an interactive prompt. When asked, select, Use other server and press ENTER:

      Output

      Set up a new Prisma server or deploy to an existing server? You can set up Prisma for local development (based on docker-compose) Use existing database Connect to existing database Create new database Set up a local database using Docker Or deploy to an existing Prisma server: Demo server Hosted demo environment incl. database (requires login) ❯ Use other server Manually provide endpoint of a running Prisma server

      You will then provide the endpoint of your server that is acting as the Prisma server. It will look something like: http://SERVER_IP_ADDRESS:4466. It is key that the endpoint begins with http (or https) and has the port number indicated.

      Output

      Enter the endpoint of your Prisma server http://SERVER_IP_ADDRESS:4466

      For the management API secret, enter in the phrase or password that you indicated earlier in the configuration file:

      Output

      Enter the management API secret my-secret

      For the subsequent options, you can choose the default variables by pressing ENTER for the service name and service stage:

      Output

      Choose a name for your service hello-world Choose a name for your stage dev

      You will also be given a choice on a programming language for the Prisma client. In this case, you can choose your preferred language. You can read more about the client here.

      Output

      Select the programming language for the generated Prisma client (Use arrow keys) ❯ Prisma TypeScript Client Prisma Flow Client Prisma JavaScript Client Prisma Go Client Don't generate

      Once you have completed the prompt, you will see the following output that confirms the selections you made:

      Output

      Created 3 new files: prisma.yml Prisma service definition datamodel.prisma GraphQL SDL-based datamodel (foundation for database) .env Env file including PRISMA_API_MANAGEMENT_SECRET Next steps: 1. Open folder: cd hello-world 2. Deploy your Prisma service: prisma deploy 3. Read more about deploying services: http://bit.ly/prisma-deploy-services

      Move into the hello-world directory:

      Sync these changes to your server with prisma deploy. This sends the information to the Prisma server from your local machine and creates the Prisma service on the Prisma server:

      Note: Running prisma deploy again will update your Prisma service.

      Your output will look something like:

      Output

      Creating stage dev for service hello-world ✔ Deploying service `hello-world` to stage 'dev' to server 'default' 468ms Changes: User (Type) + Created type `User` + Created field `id` of type `GraphQLID!` + Created field `name` of type `String!` + Created field `updatedAt` of type `DateTime!` + Created field `createdAt` of type `DateTime!` Applying changes 716ms Your Prisma GraphQL database endpoint is live: HTTP: http://SERVER_IP_ADDRESS:4466/hello-world/dev WS: ws://SERVER_IP_ADDRESS:4466/hello-world/dev

      The output shows that Prisma has updated your database according to your datamodel (created in the prisma init step) with a type User. Types are an essential part of a datamodel; they represent an item from your application, and each type contains multiple fields. For your datamodel the associated fields describing the user are: the user’s ID, name, time they were created, and time they were updated.

      If you run into issues at this stage and get a different output, double check that you entered all of the fields correctly during the interactive prompt. You can do so by reviewing the contents of the prisma.yml file.

      With your Prisma service running, you can connect to two different endpoints:

      • The management interface, available at http://SERVER_IP_ADDRESS:4466/management, where you can manage and deploy Prisma services.

      • The GraphQL API for your Prisma service, available at http://SERVER_IP_ADDRESS:4466/hello-world/dev.

      GraphQL API exploring _Your Project_

      You have successfully set up and deployed your Prisma server. You can now explore queries and mutations in GraphQL.

      Step 4 — Running an Example Query

      To explore another Prisma use case, you can experiment with the GraphQL playground tool, which is an open-source GraphQL integrated development environment (IDE) on your server. To access it, visit your endpoint in your browser from the previous step:

      http://SERVER_IP_ADDRESS:4466/hello-world/dev
      

      A mutation is a GraphQL term that describes a way to modify — create, update, or delete (CRUD) — data in the backend via GraphQL. You can send a mutation to create a new user and explore the functionality. To do this, run the following mutation in the left-hand side of the page:

      mutation {
        createUser(data: { name: "Alice" }) {
          id
          name
        }
      }
      

      Once you press the play button, you will see the results on the right-hand side of the page.
      GraphQL Playground Creating a New User

      Subsequently, if you want to look up a user by using the ID column in the database, you can run the following query:

      query {
        user(where: { id: "cjkar2d62000k0847xuh4g70o" }) {
          id
          name
        }
      }
      

      You now have a Prisma server and service up and running on your server, and you have run test queries in GraphQL's IDE.

      Conclusion

      You have a functioning Prisma setup on your server. You can see some additional Prisma use cases and next steps in the Getting Started Guide or explore Prisma's feature set in the Prisma Docs. Once you have completed all of the steps in this tutorial, you have a number of options to verify your connection to the database, one possibility is using the Prisma Client.



      Source link

      Install Plex Media Server on Ubuntu 18.04 Using Salt Masterless


      Updated by Linode Contributed by Linode

      Plex is a media server that allows you to stream video and audio content that you own to many different types of devices. In this guide you will learn how to use a masterless Salt minion to set up a Plex server, attach and use a Block Storage Volume, and how to connect to your media server to stream content to your devices.

      Before You Begin

      1. Familiarize yourself with our Getting Started guide and complete the steps for setting your Linode’s hostname and timezone.

      2. Follow the steps in the How to Secure Your Server guide.

      3. Update your system:

        sudo apt-get update && sudo apt-get upgrade
        
      4. You will need to create a Block Storage Volume and attach it to your Linode. You will format and mount the drive as part of this guide. This volume will be used to store your media, so you should pick a size that’s appropriate for your media collection, though you can resize the volume later if you need more storage. For more on Block Storage, see our How to Use Block Storage guide.

      5. Plex requires an account to use their service. Visit the Plex website to sign up for an account if you do not already have one.

      Note

      The steps in this guide require root privileges. Be sure to run the steps below with the sudo prefix. For more information on privileges, see our Users and Groups guide.

      Prepare the Salt Minion

      1. On your Linode, create the /srv/salt and /srv/pillar directories. These are where the Salt state files and Pillar files will be housed.

        mkdir /srv/salt && mkdir /srv/pillar
        
      2. Install salt-minion via the Salt bootstrap script:

        curl -L https://bootstrap.saltstack.com -o bootstrap_salt.sh
        sudo sh bootstrap_salt.sh
        
      3. The Salt minion will use the official Plex Salt Formula, which is hosted on the SaltStack GitHub repository. In order to use a Salt formula hosted on an external repository, you will need GitPython installed. Install GitPython:

        sudo apt-get install python-git
        

      Modify the Salt Minion Configuration

      1. Because the Salt minion is running in masterless mode, you will need to modify the minion configuration file (/etc/salt/minion) to instruct Salt to look for state files locally. Open the minion configuration file in a text editor, uncomment the line #file_client: remote, and set it to local:

        /etc/salt/minion
        1
        2
        3
        4
        5
        6
        7
        8
        9
        
        ...
        
        # Set the file client. The client defaults to looking on the master server for
        # files, but can be directed to look at the local file directory setting
        # defined below by setting it to "local". Setting a local file_client runs the
        # minion in masterless mode.
        file_client: local
        
        ...
      2. There are some configuration values that do not normally exist in /etc/salt/minion which you will need to add in order to run your minion in masterless mode. Copy the following lines into the end of /etc/salt/minion:

        /etc/salt/minion
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        
        ...
        
        fileserver_backend:
          - roots
          - gitfs
        
        gitfs_remotes:
          - https://github.com/saltstack-formulas/plex-formula.git
        
        gitfs_provider: gitpython

        The fileserver_backend block instructs the Salt minion to look for Salt configuration files in two places. First, it tells Salt to look for Salt state files in our minion’s roots backend (/srv/salt). Secondly, it instructs Salt to use the Git Fileserver (gitfs) to look for Salt configuration files in any Git remote repositories that have been named in the gitfs_remotes section. The address for the Plex Salt formula’s Git repository is included in the gitfs_remotes section.

        Note

        It is best practice to create a fork of the Plex formula’s Git repository on GitHub and to add your fork’s Git repository address in the gitfs_remotes section. This will ensure that any further changes to the upstream Plex formula which might break your current configuration can be reviewed and handled accordingly, before applying them.

        Lastly, GitPython is specified as the gitfs_provider.

      Create the Salt State Tree

      1. Create a Salt state top file at /srv/salt/top.sls and copy in the following configuration. This file tells Salt to look for state files in the plex folder of the Plex formula’s Git repository, and for a state files named disk.sls and directory.sls, which you will create in the next steps.

        /srv/salt/top.sls
        1
        2
        3
        4
        5
        
        base:
          '*':
            - plex
            - disk
            - directory
      2. Create the disk.sls file in /srv/salt:

        /srv/salt/disk.sls
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        
        disk.format:
          module.run:
            - device: /dev/disk/by-id/scsi-0Linode_Volume_{{ pillar['volume_name'] }}
            - fs_type: ext4
        
        /mnt/plex:
          mount.mounted:
            - device: /dev/disk/by-id/scsi-0Linode_Volume_{{ pillar['volume_name'] }}
            - fstype: ext4
            - mkmnt: True
            - persist: True

        This file instructs Salt to prepare your Block Storage Volume for use with Plex. It first formats your Block Storage Volume with the ext4 filesystem type by using the disk.format Salt module, which can be run in a state file using module.run. Then disk.sls instructs Salt to mount your volume at /mnt/plex, creating the mount target if it does not already exist with mkmnt, and persisting the mount to /etc/fstab so that the volume is always mounted at boot.

      3. Create the directory.sls file in /srv/salt:

        /srv/salt/directory.sls
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        
        /mnt/plex/plex-media:
          file.directory:
            - require:
              - mount: /mnt/plex
            - user: username
            - group: plex
        
        /mnt/plex/plex-media/movies:
          file.directory:
            - require:
              - mount: /mnt/plex
            - user: username
            - group: plex
        
        /mnt/plex/plex-media/television:
          file.directory:
            - require:
              - mount: /mnt/plex
            - user: username
            - group: plex

        The directories that are created during this step are for organizational purposes, and will house your media. Make sure you replace username with the name of the limited user account you created when following the How to Secure Your Server guide. The location of the directories is the volume you mounted in the previous step. If you wish to add more directories, perhaps one for your music media, you can do so here, just be sure to include the - require block, as this prevents Salt from trying to create the directory before the Block Storage Volume has been mounted.

      4. Go to the Plex Media Server download page and note the most recent version of their Linux distribution. At the time of writing, the most recent version is 1.13.9.5456-ecd600442. Create the plex.sls Pillar file in /srv/pillar and change the Plex version number and the name of your Block Storage Volume as necessary:

        /srv/pillar/plex.sls
        1
        2
        3
        
        plex:
          version: 1.13.9.5456-ecd600442
        volume_name: plex
      5. Create the Salt Pillar top file in /srv/pillar. This file will instruct Salt to look for the plex.sls Pillar file you created in the previous step.

        /srv/pillar/top.sls
      6. Apply your Salt state locally using salt-call:

        salt-call --local state.apply
        

        You should see a list of the changes Salt applied to your system. You have successfully installed Plex using Salt.

      Set Up Plex

      Initial Configuration

      1. You’ll need to create an SSH tunnel to your Linode to connect to Plex’s web UI. On your local computer, run the following command, replacing <your_ip_address> with your Plex server’s IP address.:

        ssh username@<your_ip_address> -L 8888:localhost:32400
        
      2. In a browser, navigate to http://localhost:8888/web/.

      3. Sign in with your Plex username and password.

      4. Name your media server. This example uses the name linode-plex. Be sure to check the box that reads Allow me to access my media outside my home and then click Next.

        Name your media server

      Organize Your Media

      1. Click on the Add Library button:

        Click on Add Media

      2. Select Movies and click Next:

        Select Movies and click next

      3. Click Browse for Media Folder and select the appropriate folder at /mnt/plex/plex-media/movies. Then click Add:

        Select the appropriate folder

      4. Repeat the process to add your ‘Television’ folder.

      5. When you are done adding your libraries, click Add Library.

      6. To continue the configuration process, click Next.

      7. Click on Get Plex Apps to download the appropriate Plex client for your device. Then click Done.

        Download the appropriate client for your device

      8. In the future you can add more libraries by hovering over the menu and clicking the plus sign (+) next to LIBRARIES.

        Add more libraries

      DLNA is a protocol that incorporates Universal Plug and Play (or UPnP) standards for digital media sharing across devices. If you do not wish to make use of it, it’s recommended that you disable this feature, as it is openly connectable on port 1900. From the Plex web interface, click the wrench icon in the upper right corner, and navigate to the DLNA section under SETTINGS. Uncheck Enable the DLNA server, and click Save Changes:

      Disable DLNA

      Connect to Your Plex Server

      1. Visit the Plex Apps download page or the app store on your device to download Plex Media Player if you have not already done so.

      2. Open your Plex app. The example provided here will use the Plex Media Player for macOS.

      3. Sign in to Plex.

      4. On the left there’s a dropdown menu where you can select your server by the name you chose. Select your server.

        Connect to your Plex Server

      5. You are now able to stream your content with Plex.

        Plex's macOS App

      1. You can use SCP to transfer media to your server from your local computer. Replace your username and 123.456.7.8 with the IP address of your Linode.

        scp example_video.mp4 username@123.456.7.8:/mnt/plex/plex-media/movies
        
      2. Once you’ve transferred files to your Plex media server, you may need to scan for new files before they show up in your Library. Click on the ellipsis next to a Library and select Scan Library Files.

        Scan your Library for new files

      More Information

      You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

      Find answers, ask questions, and help others.

      This guide is published under a CC BY-ND 4.0 license.



      Source link

      How to Install and Configure pgAdmin 4 in Server Mode


      Introduction

      pgAdmin is an open-source administration and development platform for PostgreSQL and its related database management systems. Written in Python and jQuery, it supports all the features found in PostgreSQL. You can use pgAdmin to do everything from writing basic SQL queries to monitoring your databases and configuring advanced database architectures.

      In this tutorial, we’ll walk through the process of installing and configuring the latest version of pgAdmin onto an Ubuntu 18.04 server, accessing pgAdmin through a web browser, and connecting it to a PostgreSQL database on your server.

      Prerequisites

      To complete this tutorial, you will need:

      Step 1 — Installing pgAdmin and its Dependencies

      As of this writing, the most recent version of pgAdmin is pgAdmin 4, while the most recent version available through the official Ubuntu repositories is pgAdmin 3. pgAdmin 3 is no longer supported though, and the project maintainers recommend installing pgAdmin 4. In this step, we will go over the process of installing the latest version of pgAdmin 4 within a virtual environment (as recommended by the project’s development team) and installing its dependencies using apt.

      To begin, update your server’s package index if you haven’t done so recently:

      Next, install the following dependencies. These include libgmp3-dev, a multiprecision arithmetic library; libpq-dev, which includes header files and a static library that helps communication with a PostgreSQL backend; and libapache2-mod-wsgi-py3, an Apache module that allows you to host Python-based web applications within Apache:

      • sudo apt install libgmp3-dev libpq-dev libapache2-mod-wsgi-py3

      Following this, create a few directories where pgAdmin will store its sessions data, storage data, and logs:

      • sudo mkdir -p /var/lib/pgadmin4/sessions
      • sudo mkdir /var/lib/pgadmin4/storage
      • sudo mkdir /var/log/pgadmin4

      Then, change ownership of these directories to your non-root user and group. This is necessary because they are currently owned by your root user, but we will install pgAdmin from a virtual environment owned by your non-root user, and the installation process involves creating some files within these directories. After the installation, however, we will change the ownership over to the www-data user and group so it can be served to the web:

      • sudo chown -R sammy:sammy /var/lib/pgadmin4
      • sudo chown -R sammy:sammy /var/log/pgadmin4

      Next, open up your virtual environment. Navigate to the directory your programming environment is in and activate it. Following the naming conventions of the prerequisite Python 3 tutorial, we’ll go to the environments directory and activate the my_env environment:

      • cd environments/
      • source my_env/bin/activate

      Following this, download the pgAdmin 4 source code onto your machine. To find the latest version of the source code, navigate to the pgAdmin 4 (Python Wheel) Download page and click the link for the latest version (v3.4, as of this writing). This will take you to a Downloads page on the PostgreSQL website. Once there, copy the file link that ends with .whl — the standard built-package format used for Python distributions. Then go back to your terminal and run the following wget command, making sure to replace the link with the one you copied from the PostgreSQL site, which will download the .whl file to your server:

      • wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v3.4/pip/pgadmin4-3.4-py2.py3-none-any.whl

      Next install the wheel package, the reference implementation of the wheel packaging standard. A Python library, this package serves as an extension for building wheels and includes a command line tool for working with .whl files:

      • python -m pip install wheel

      Then install pgAdmin 4 package with the following command:

      • python -m pip install pgadmin4-3.4-py2.py3-none-any.whl

      That takes care of installing pgAdmin and its dependencies. Before connecting it to your database, though, there are a few changes you’ll need to make to the program’s configuration.

      Step 2 — Configuring pgAdmin 4

      Although pgAdmin has been installed on your server, there are still a few steps you must go through to ensure it has the permissions and configurations needed to allow it to correctly serve the web interface.

      pgAdmin’s main configuration file, config.py, is read before any other configuration file. Its contents can be used as a reference point for further configuration settings that can be specified in pgAdmin’s other config files, but to avoid unforeseen errors, you should not edit the config.py file itself. We will add some configuration changes to a new file, named config_local.py, which will be read after the primary one.

      Create this file now using your preferred text editor. Here, we will use nano:

      • nano my_env/lib/python3.6/site-packages/pgadmin4/config_local.py

      In your editor, add the following content:

      environments/my_env/lib/python3.6/site-packages/pgadmin4/config_local.py

      LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
      SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
      SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
      STORAGE_DIR = '/var/lib/pgadmin4/storage'
      SERVER_MODE = True
      

      Here are what these five directives do:

      • LOG_FILE: this defines the file in which pgAdmin’s logs will be stored.
      • SQLITE_PATH: pgAdmin stores user-related data in an SQLite database, and this directive points the pgAdmin software to this configuration database. Because this file is located under the persistent directory /var/lib/pgadmin4/, your user data will not be lost after you upgrade.
      • SESSION_DB_PATH: specifies which directory will be used to store session data.
      • STORAGE_DIR: defines where pgAdmin will store other data, like backups and security certificates.
      • SERVER_MODE: setting this directive to True tells pgAdmin to run in Server mode, as opposed to Desktop mode.

      Notice that each of these file paths point to the directories you created in Step 1.

      After adding these lines, save and close the file (press CTRL + X, followed by Y and then ENTER). With those configurations in place, run the pgAdmin setup script to set your login credentials:

      • python my_env/lib/python3.6/site-packages/pgadmin4/setup.py

      After running this command, you will see a prompt asking for your email address and a password. These will serve as your login credentials when you access pgAdmin later on, so be sure to remember or take note of what you enter here:

      Output

      . . . Enter the email address and password to use for the initial pgAdmin user account: Email address: sammy@example.com Password: Retype password:

      Following this, deactivate your virtual environment:

      Recall the file paths you specified in the config_local.py file. These files are held within the directories you created in Step 1, which are currently owned by your non-root user. They must, however, be accessible by the user and group running your web server. By default on Ubuntu 18.04, these are the www-data user and group, so update the permissions on the following directories to give www-data ownership over both of them:

      • sudo chown -R www-data:www-data /var/lib/pgadmin4/
      • sudo chown -R www-data:www-data /var/log/pgadmin4/

      With that, pgAdmin is fully configured. However, the program isn't yet being served from your server, so it remains inaccessible. To resolve this, we will configure Apache to serve pgAdmin so you can access its user interface through a web browser.

      Step 3 — Configuring Apache

      The Apache web server uses virtual hosts to encapsulate configuration details and host more than one domain from a single server. If you followed the prerequisite Apache tutorial, you may have set up an example virtual host file under the name example.com.conf, but in this step we will create a new one from which we can serve the pgAdmin web interface.

      To begin, make sure you're in your root directory:

      Then create a new file in your /sites-available/ directory called pgadmin4.conf. This will be your server’s virtual host file:

      • sudo nano /etc/apache2/sites-available/pgadmin4.conf

      Add the following content to this file, being sure to update the highlighted parts to align with your own configuration:

      /etc/apache2/sites-available/pgadmin4.conf

      <VirtualHost *>
          ServerName your_server_ip
      
          WSGIDaemonProcess pgadmin processes=1 threads=25 python-home=/home/sammy/environments/my_env
          WSGIScriptAlias / /home/sammy/environments/my_env/lib/python3.6/site-packages/pgadmin4/pgAdmin4.wsgi
      
          <Directory "/home/sammy/environments/my_env/lib/python3.6/site-packages/pgadmin4/">
              WSGIProcessGroup pgadmin
              WSGIApplicationGroup %{GLOBAL}
              Require all granted
          </Directory>
      </VirtualHost>
      

      Save and close the virtual host file. Next, use the a2dissite script to disable the default virtual host file, 000-default.conf:

      • sudo a2dissite 000-default.conf

      Note: If you followed the prerequisite Apache tutorial, you may have already disabled 000-default.conf and set up an example virtual host configuration file (named example.com.conf in the prerequisite). If this is the case, you will need to disable the example.com.conf virtual host file with the following command:

      • sudo a2dissite example.com.conf

      Then use the a2ensite script to enable your pgadmin4.conf virtual host file. This will create a symbolic link from the virtual host file in the /sites-available/ directory to the /sites-enabled/ directory:

      • sudo a2ensite pgadmin4.conf

      Following this, test that your configuration file’s syntax is correct:

      If your configuration file is all in order, you will see Syntax OK. If you see an error in the output, reopen the pgadmin4.conf file and double check that your IP address and file paths are all correct, then rerun the configtest.

      Once you see Syntax OK in your output, restart the Apache service so it reads your new virtual host file:

      • sudo systemctl restart apache2

      pgAdmin is now fully installed and configured. Next, we'll go over how to access pgAdmin from a browser before connecting it to your PostgreSQL database.

      Step 4 — Accessing pgAdmin

      On your local machine, open up your preferred web browser and navigate to your server’s IP address:

      http://your_server_ip
      

      Once there, you’ll be presented with a login screen similar to the following:

      pgAdmin login screen

      Enter the login credentials you defined in Step 2, and you’ll be taken to the pgAdmin Welcome Screen:

      pgAdmin Welcome Page

      Now that you've confirmed you can access the pgAdmin interface, all that's left to do is to connect pgAdmin to your PostgreSQL database. Before doing so, though, you'll need to make one minor change to your PostgreSQL superuser's configuration.

      Step 5 — Configuring your PostgreSQL User

      If you followed the prerequisite PostgreSQL tutorial, you should already have PostgreSQL installed on your server with a new superuser role and database set up.

      By default in PostgreSQL, you authenticate as database users using the "Identification Protocol," or "ident," authentication method. This involves PostgreSQL taking the client's Ubuntu username and using it as the allowed database username. This can allow for greater security in many cases, but it can also cause issues in instances where you'd like an outside program, such as pgAdmin, to connect to one of your databases. To resolve this, we will set a password for this PostgreSQL role which will allow pgAdmin to connect to your database.

      From your terminal, open the PostgreSQL prompt under your superuser role:

      From the PostgreSQL prompt, update the user profile to have a strong password of your choosing:

      • ALTER USER sammy PASSWORD 'password';

      Then exit the PostgreSQL prompt:

      Next, go back to the pgAdmin 4 interface in your browser, and locate the Browser menu on the left hand side. Right-click on Servers to open a context menu, hover your mouse over Create, and click Server….

      Create Server context menu

      This will cause a window to pop up in your browser in which you'll enter info about your server, role, and database.

      In the General tab, enter the name for this server. This can be anything you'd like, but you may find it helpful to make it something descriptive. In our example, the server is named Sammy-server-1.

      Create Server - General tab

      Next, click on the Connection tab. In the Host name/address field, enter localhost. The Port should be set to 5432 by default, which will work for this setup, as that's the default port used by PostgreSQL.

      In the Maintenance database field, enter the name of the database you'd like to connect to. Note that this database must already be created on your server. Then, enter the PostgreSQL username and password you configured previously in the Username and Password fields, respectively.

      Create Server - Connection tab

      The empty fields in the other tabs are optional, and it's only necessary that you fill them in if you have a specific setup in mind in which they're required. Click the Save button, and the database will appear under the Servers in the Browser menu.

      You've successfully connected pgAdmin4 to your PostgreSQL database. You can do just about anything from the pgAdmin dashboard that you would from the PostgreSQL prompt. To illustrate this, we will create an example table and populate it with some sample data through the web interface.

      Step 6 — Creating a Table in the pgAdmin Dashboard

      From the pgAdmin dashboard, locate the Browser menu on the left-hand side of the window. Click on the plus sign (+) next to Servers (1) to expand the tree menu within it. Next, click the plus sign to the left of the server you added in the previous step (Sammy-server-1 in our example), then expand Databases, the name of the database you added (sammy, in our example), and then Schemas (1). You should see a tree menu like the following:

      Expanded Browser tree menu

      Right-click the Tables list item, then hover your cursor over Create and click Table….

      Create Table context menu

      This will open up a Create-Table window. Under the General tab of this window, enter a name for the table. This can be anything you'd like, but to keep things simple we'll refer to it as table-01.

      Create Table - General tab

      Then navigate to the Columns tab and click the + sign in the upper right corner of the window to add some columns. When adding a column, you're required to give it a Name and a Data type, and you may need to choose a Length if it's required by the data type you've selected.

      Additionally, the official PostgreSQL documentation states that adding a primary key to a table is usually best practice. A primary key is a constraint that indicates a specific column or set of columns that can be used as a special identifier for rows in the table. This isn't a requirement, but if you'd like to set one or more of your columns as the primary key, toggle the switch at the far right from No to Yes.

      Click the Save button to create the table.

      Create Table - Columns Tab with Primary Key turned on

      By this point, you've created a table and added a couple columns to it. However, the columns don't yet contain any data. To add data to your new table, right-click the name of the table in the Browser menu, hover your cursor over Scripts and click on INSERT Script.

      INSERT script context menu

      This will open a new panel on the dashboard. At the top you'll see a partially-completed INSERT statement, with the appropriate table and column names. Go ahead and replace the question marks (?) with some dummy data, being sure that the data you add aligns with the data types you selected for each column. Note that you can also add multiple rows of data by adding each row in a new set of parentheses, with each set of parentheses separated by a comma as shown in the following example.

      If you'd like, feel free to replace the partially-completed INSERT script with this example INSERT statement:

      INSERT INTO public."table-01"(
          col1, col2, col3)
          VALUES ('Juneau', 14, 337), ('Bismark', 90, 2334), ('Lansing', 51, 556);
      

      Example INSERT statement

      Click on the lightning bolt icon () to execute the INSERT statement. To view the table and all the data within it, right-click the name of your table in the Browser menu once again, hover your cursor over View/Edit Data, and select All Rows.

      View/Edit Data, All Rows context menu

      This will open another new panel, below which, in the lower panel's Data Output tab, you can view all the data held within that table.

      View Data - example data output

      With that, you've successfully created a table and populated it with some data through the pgAdmin web interface. Of course, this is just one method you can use to create a table through pgAdmin. For example, it's possible to create and populate a table using SQL instead of the GUI-based method described in this step.

      Conclusion

      In this guide, you learned how to install pgAdmin 4 from a Python virtual environment, configure it, serve it to the web with Apache, and how to connect it to a PostgreSQL database. Additionally, this guide went over one method that can be used to create and populate a table, but pgAdmin can be used for much more than just creating and editing tables.

      For more information on how to get the most out of all of pgAdmin's features, we encourage you to review the project's documentation. You can also learn more about PostgreSQL through our Community tutorials on the subject.



      Source link